vertex assigned to variable o1 was deleted - large create statements

[avowkind]

I need some help understanding this error message:

{"message":"create failed","error":"error: vertex assigned to variable o1 was deleted"}

I have a LARGE create statement that is posting in a batch of environmental observations. It looks up vertices of PropertyType, FeatureOfInterest and Dataset and then creates Observations linked to those vertices. example below.

This creates 128 observations in each request. After running some count of requests successfully I get the above error.

This seems to imply that either:

• during execution of the query the vertex o1 that is created is lost or deleted by the time we are adding the edges.
• or perhaps an o1 vertex previously created and/or deleted at some point in the past is being found and used again - giving the error.

I'm getting the error while running an Extract/Transform/Load using Apache Airflow. This is moving data from an operational system to the graph database. It is possible that we might try to load the same dataset twice.

This is what I see in the Postgresql logs

2024-02-26 10:37:58.955 NZDT [31201] ERROR: vertex assigned to variable o1 was deleted 2024-02-26 10:37:58.955 NZDT [31201] STATEMENT:
body is > 64K so I will post in a file

[avowkind]

failing_create.txt The failing SQL

[avowkind]

Perhaps not due to the size of the request:

This also fails:

LOAD 'age';
SET search_path = ag_catalog, "$user", public;



SELECT * from cypher('fishpond', $$ 
MATCH (water_temperature_celsius:PropertyType {id: 'water_temperature_celsius'}),
(comment:PropertyType {id: 'comment'})
MATCH (pfr_feature_tank_A1:Feature {id: 'pfr:nelson:tank:A01'}),
(pfr_feature_tank_A2:Feature {id: 'pfr:nelson:tank:A02'})
CREATE (o1:Observation {id:"1",phenomenonTime:"2020-01-01T00:00:00Z",resultTime:"2020-01-01T00:00:00Z",result:20,parameters:{parameter1:"value1",parameter2:"value2"},resultQuality:"good",featureOfInterest:"pfr:nelson:tank:A01",observedProperty:"water_temperature_celsius"}),
(o2:Observation {id:"2",phenomenonTime:"2020-01-01T00:00:00Z",resultTime:"2020-01-01T00:00:00Z",result:"taken by hand",parameters:{operator:"[email protected]"},resultQuality:"good",featureOfInterest:"pfr:nelson:tank:A01",observedProperty:"comment"}),
(o3:Observation {id:"3",phenomenonTime:"2020-01-01T01:00:00Z",resultTime:"2020-01-01T01:00:00Z",result:20.1,parameters:{parameter1:"value1",parameter2:"value2"},resultQuality:"good",featureOfInterest:"pfr:nelson:tank:A01",observedProperty:"water_temperature_celsius"}),
(o4:Observation {id:"4",phenomenonTime:"2020-01-01T01:00:00Z",resultTime:"2020-01-01T01:00:00Z",result:22.2,parameters:{parameter1:"value1",parameter2:"value2"},resultQuality:"good",featureOfInterest:"pfr:nelson:tank:A02",observedProperty:"water_temperature_celsius"}),
(o5:Observation {id:"5",phenomenonTime:"2020-01-01T02:00:00Z",resultTime:"2020-01-01T01:00:00Z",result:22.2,parameters:{parameter1:"value1",parameter2:"value2"},resultQuality:"good",featureOfInterest:"pfr:nelson:tank:A02",observedProperty:"water_temperature_celsius"})
CREATE (o1)-[:observedProperty]->(water_temperature_celsius),
(o1)-[:featureOfInterest]->(pfr_feature_tank_A1),
(o2)-[:observedProperty]->(comment),
(o2)-[:featureOfInterest]->(pfr_feature_tank_A1),
(o3)-[:observedProperty]->(water_temperature_celsius),
(o3)-[:featureOfInterest]->(pfr_feature_tank_A1),
(o4)-[:observedProperty]->(water_temperature_celsius),
(o4)-[:featureOfInterest]->(pfr_feature_tank_A2),
(o5)-[:observedProperty]->(water_temperature_celsius),
(o5)-[:featureOfInterest]->(pfr_feature_tank_A2)
RETURN o1
$$) as (o1 ag_catalog.agtype);

[avowkind]

It seems that the issue is actually related to the fact that the subclause (comment:PropertyType {id: 'comment'}) matches two vertices.

[jrgemignani]

@avowkind I will try to look into this tomorrow.

[github-actions[bot]]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[MuhammadTahaNaveed]

@avowkind Sorry for the late response. Is this still an issue? @jrgemignani Were you able to reproduce this issue?

[jrgemignani]

@MuhammadTahaNaveed I have not had an opportunity to look into this yet, sorry.

[jrgemignani]

@avowkind Let me know if this is still an issue.

[avowkind]

Its no longer an issue for me per se as I fixed my immediate problem by ensuring I did not have multiple matching vertices in the create statement.

It seems that the issue was related to the fact that the subclause (comment:PropertyType {id: 'comment'}) matched two vertices.

However, I might suggest that the issue remains in that the error message is very unrelated to the cause of the problem making it difficult to diagnose.

[jrgemignani]

@avowkind Ty for letting us know. If it pops up again, in a more repeatable scenario, don't hesitate to let us know.