When it executes VLE(Variable Length Edge) query, why it unable to find the data in the database. #2143

ToLboo · 2024-12-26T10:52:02Z

I am experiencing a similar issue with . Whenever I run the following query:

SELECT * FROM cypher('multi_in_1_graph', $$ MATCH (cs:CLOSED_SHELL {id: 32722, file_name: "Jaguar_E_Type_roadster"})-[*1..3]->(el:EDGE_LOOP) RETURN el.id AS id, el $$) AS (id integer, el agtype);

It always throws the following error on the first execution:

SQL state: XX000

Subsequent executions do not throw this error but always return empty results. However, when I run this query:

SELECT * FROM cypher('multi_in_1_graph', $$ MATCH (cs:CLOSED_SHELL {id: 32722, file_name: "Jaguar_E_Type_roadster"})-[]->()-[]->()-[]->(el:EDGE_LOOP) RETURN el.id AS id, el $$) AS (id integer, el agtype);

It successfully retrieves valid results.

Could you please help me understand why this is happening and how to resolve the issue?

The text was updated successfully, but these errors were encountered:

MuhammadTahaNaveed · 2024-12-27T06:32:56Z

@ToLboo Can you please share the following information neccessary for me to reproduce the issue

AGE version
PostgreSQL version
What data setup do we need to do?

ToLboo · 2024-12-30T10:39:25Z

Thank you for your reply！
My PG version is 15.8 (Ubuntu15.8.1 - pgdg22.04+1)，age version is 15.0.
I think you can reproduce the issue on any data. If not, you can use the following edge table and node table in CSV format.
G_010101.zip

Mocuto · 2025-01-06T13:03:17Z

Also experiencing this issue, running apache age for PG12 cloned from latest master branch.

ToLboo · 2025-01-17T03:50:00Z

@Mocuto Is the problem solved now?

MuhammadTahaNaveed · 2025-01-18T18:18:46Z

Hi @ToLboo,

I have tried reproducing the issue, but am unable to. Could you provide me the complete error message?

psql (15.8)
Type "help" for help.

issue_2148=# CREATE EXTENSION age; SET search_path=ag_catalog;
CREATE EXTENSION
SET

issue_2148=# \dx age
             List of installed extensions
 Name | Version |   Schema   |      Description       
------+---------+------------+------------------------
 age  | 1.5.0   | ag_catalog | AGE database extension
(1 row)

issue_2148=# SELECT create_graph('graph');
NOTICE:  graph "graph" has been created
 create_graph 
--------------
 
(1 row)

issue_2148=# SELECT create_vlabel('graph', 'ADVANCED_FACE');
NOTICE:  VLabel "ADVANCED_FACE" has been created
 create_vlabel 
---------------
 
(1 row)

issue_2148=# SELECT create_vlabel('graph', 'CLOSED_SHELL');
NOTICE:  VLabel "CLOSED_SHELL" has been created
 create_vlabel 
---------------
 
(1 row)

issue_2148=# SELECT create_vlabel('graph', 'EDGE_LOOP');
NOTICE:  VLabel "EDGE_LOOP" has been created
 create_vlabel 
---------------
 
(1 row)

issue_2148=# SELECT create_vlabel('graph', 'FACE_BOUND');
NOTICE:  VLabel "FACE_BOUND" has been created
 create_vlabel 
---------------
 
(1 row)

issue_2148=# SELECT load_labels_from_file('graph', 'ADVANCED_FACE', '/path/to/G_010101/G_010101_Front_Floor_Asy/vertex/ADVANCED_FACE.csv');
 load_labels_from_file 
-----------------------
 
(1 row)

issue_2148=# SELECT load_labels_from_file('graph', 'CLOSED_SHELL', '/path/to/G_010101/G_010101_Front_Floor_Asy/vertex/CLOSED_SHELL.csv');
 load_labels_from_file 
-----------------------
 
(1 row)

issue_2148=# SELECT load_labels_from_file('graph', 'EDGE_LOOP', '/path/to/G_010101/G_010101_Front_Floor_Asy/vertex/EDGE_LOOP.csv');
 load_labels_from_file 
-----------------------
 
(1 row)

issue_2148=# SELECT load_labels_from_file('graph', 'FACE_BOUND', '/path/to/G_010101/G_010101_Front_Floor_Asy/vertex/FACE_BOUND.csv');
 load_labels_from_file 
-----------------------
 
(1 row)

issue_2148=# SELECT create_elabel('graph', 'E_ADVANCED_FACE_2_FACE_BOUND');
NOTICE:  ELabel "E_ADVANCED_FACE_2_FACE_BOUND" has been created
 create_elabel 
---------------
 
(1 row)

issue_2148=# SELECT create_elabel('graph', 'E_CLOSED_SHELL_2_ADVANCED_FACE');
NOTICE:  ELabel "E_CLOSED_SHELL_2_ADVANCED_FACE" has been created
 create_elabel 
---------------
 
(1 row)

issue_2148=# SELECT create_elabel('graph', 'E_FACE_BOUND_2_EDGE_LOOP');
NOTICE:  ELabel "E_FACE_BOUND_2_EDGE_LOOP" has been created
 create_elabel 
---------------
 
(1 row)

issue_2148=# SELECT load_edges_from_file('graph', 'E_ADVANCED_FACE_2_FACE_BOUND', '/path/to/G_010101/G_010101_Front_Floor_Asy/edges/E_ADVANCED_FACE_2_FACE_BOUND.csv');
 load_edges_from_file 
----------------------
 
(1 row)

issue_2148=# SELECT load_edges_from_file('graph', 'E_CLOSED_SHELL_2_ADVANCED_FACE', '/path/to/G_010101/G_010101_Front_Floor_Asy/edges/E_CLOSED_SHELL_2_ADVANCED_FACE.csv');
 load_edges_from_file 
----------------------
 
(1 row)

issue_2148=# SELECT load_edges_from_file('graph', 'E_FACE_BOUND_2_EDGE_LOOP', '/path/to/G_010101/G_010101_Front_Floor_Asy/edges/E_FACE_BOUND_2_EDGE_LOOP.csv');
 load_edges_from_file 
----------------------
 
(1 row)

issue_2148=# SELECT * FROM cypher('graph', $$ MATCH (cs:CLOSED_SHELL {id: "26", file_name: "010101_Front_Floor_Asy"})-[*1..3]->(el:EDGE_LOOP) RETURN el.id AS id, el $$) AS (id integer, el agtype);
  id  |                                                                                                                                                           el                          
                                                                                                                                 
------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
   29 | {"id": 1407374883553309, "label": "EDGE_LOOP", "properties": {"id": "29", "ids": "[30,228,341,446]", "name": "''", "__id__": 29, "belong": "010101_Front_Floor_Asy.stp0000000000001220
", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "30,228,341,446"}}::vertex
  468 | {"id": 1407374883553748, "label": "EDGE_LOOP", "properties": {"id": "468", "ids": "[469,470,568,569]", "name": "''", "__id__": 468, "belong": "010101_Front_Floor_Asy.stp0000000000001
220", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "469,470,568,569"}}::vertex
  669 | {"id": 1407374883553949, "label": "EDGE_LOOP", "properties": {"id": "669", "ids": "[670,831]", "name": "''", "__id__": 669, "belong": "010101_Front_Floor_Asy.stp0000000000001220", "f
ile_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "670,831"}}::vertex
  989 | {"id": 1407374883554269, "label": "EDGE_LOOP", "properties": {"id": "989", "ids": "[990,991]", "name": "''", "__id__": 989, "belong": "010101_Front_Floor_Asy.stp0000000000001220", "f
ile_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "990,991"}}::vertex
  994 | {"id": 1407374883554274, "label": "EDGE_LOOP", "properties": {"id": "994", "ids": "[995,1087,1107,1108]", "name": "''", "__id__": 994, "belong": "010101_Front_Floor_Asy.stp0000000000
001220", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "995,1087,1107,1108"}}::vertex
 1130 | {"id": 1407374883554410, "label": "EDGE_LOOP", "properties": {"id": "1130", "ids": "[1131,1132]", "name": "''", "__id__": 1130, "belong": "010101_Front_Floor_Asy.stp0000000000001220"
, "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "1131,1132"}}::vertex
 1208 | {"id": 1407374883554488, "label": "EDGE_LOOP", "properties": {"id": "1208", "ids": "[1209,1210]", "name": "''", "__id__": 1208, "belong": "010101_Front_Floor_Asy.stp0000000000001220"
, "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "1209,1210"}}::vertex
 1213 | {"id": 1407374883554493, "label": "EDGE_LOOP", "properties": {"id": "1213", "ids": "[1214,1215,1216,1217]", "name": "''", "__id__": 1213, "belong": "010101_Front_Floor_Asy.stp0000000
000001220", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "1214,1215,1216,1217"}}::vertex
(8 rows)

issue_2148=# SELECT * FROM cypher('graph', $$ MATCH (cs:CLOSED_SHELL {id: "26", file_name: "010101_Front_Floor_Asy"})-[]->()-[]->()-[]->(el:EDGE_LOOP) RETURN el.id AS id, el $$) AS (id integer, el agtype);
  id  |                                                                                                                                                           el                          
                                                                                                                                 
------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
   29 | {"id": 1407374883553309, "label": "EDGE_LOOP", "properties": {"id": "29", "ids": "[30,228,341,446]", "name": "''", "__id__": 29, "belong": "010101_Front_Floor_Asy.stp0000000000001220
", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "30,228,341,446"}}::vertex
  468 | {"id": 1407374883553748, "label": "EDGE_LOOP", "properties": {"id": "468", "ids": "[469,470,568,569]", "name": "''", "__id__": 468, "belong": "010101_Front_Floor_Asy.stp0000000000001
220", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "469,470,568,569"}}::vertex
  669 | {"id": 1407374883553949, "label": "EDGE_LOOP", "properties": {"id": "669", "ids": "[670,831]", "name": "''", "__id__": 669, "belong": "010101_Front_Floor_Asy.stp0000000000001220", "f
ile_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "670,831"}}::vertex
  989 | {"id": 1407374883554269, "label": "EDGE_LOOP", "properties": {"id": "989", "ids": "[990,991]", "name": "''", "__id__": 989, "belong": "010101_Front_Floor_Asy.stp0000000000001220", "f
ile_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "990,991"}}::vertex
  994 | {"id": 1407374883554274, "label": "EDGE_LOOP", "properties": {"id": "994", "ids": "[995,1087,1107,1108]", "name": "''", "__id__": 994, "belong": "010101_Front_Floor_Asy.stp0000000000
001220", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "995,1087,1107,1108"}}::vertex
 1130 | {"id": 1407374883554410, "label": "EDGE_LOOP", "properties": {"id": "1130", "ids": "[1131,1132]", "name": "''", "__id__": 1130, "belong": "010101_Front_Floor_Asy.stp0000000000001220"
, "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "1131,1132"}}::vertex
 1208 | {"id": 1407374883554488, "label": "EDGE_LOOP", "properties": {"id": "1208", "ids": "[1209,1210]", "name": "''", "__id__": 1208, "belong": "010101_Front_Floor_Asy.stp0000000000001220"
, "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "1209,1210"}}::vertex
 1213 | {"id": 1407374883554493, "label": "EDGE_LOOP", "properties": {"id": "1213", "ids": "[1214,1215,1216,1217]", "name": "''", "__id__": 1213, "belong": "010101_Front_Floor_Asy.stp0000000
000001220", "file_name": "010101_Front_Floor_Asy", "entity_type": "EDGE_LOOP", "related_entity": "1214,1215,1216,1217"}}::vertex
(8 rows)

ToLboo · 2025-02-21T04:32:27Z

Thank you for your reply @MuhammadTahaNaveed ! I tested it with the dataset I provided earlier, and it worked fine. It seems that the issue arises with a larger dataset (about 12GB). Each time I execute the first VLE statement, such as:
SELECT * FROM cypher('multi_in_1_graph', $$ MATCH path = (cs:CLOSED_SHELL {id: "32722"})-[*1..3]->(el:ADVANCED_FACE) RETURN el.id AS id, el $$) AS (id integer, el agtype);

It throws the error:

ERROR: insert_vertex_entry: failed due to duplicate SQL state: XX000

However, when I execute it again, it does not throw the error but returns an empty result.

jrgemignani · 2025-02-21T18:11:58Z

@ToLboo @Mocuto The issue here is due to the VLE subsystem checking for duplicate or malformed vertices or edges when running. Please see PRs #1750 & #1742 and issues #365 for details.

Additionally, it looks like you are using a version that is no longer supported and doesn't contain the above fixes to help address this. We only support PG13 and up. The newer branches have fixes that can help you address this.

What should you do?

It is recommended that you use a PG & AGE version, PG13 and up, that is supported (unsupported versions are, unsupported), thoroughly read the above PRs and issues for a more complete explanation, and run the graph_stats command against the graph in question if the problem still persists.

Remember that the newer versions have updates that allow the VLE subsystem to tell you what issues, if any, there are with the graph.

Note: It was found that the csv loader could cause this problem (PR #2044) due to how it loaded data. So, a bad load could cause issues like this.

ToLboo added the question Further information is requested label Dec 26, 2024

ToLboo changed the title ~~When it executes VLE(Variable Length Edge) query, why it search nothing ?~~ When it executes VLE(Variable Length Edge) query, why it unable to find the data in the database. Dec 26, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

When it executes VLE(Variable Length Edge) query, why it unable to find the data in the database. #2143

When it executes VLE(Variable Length Edge) query, why it unable to find the data in the database. #2143

ToLboo commented Dec 26, 2024

MuhammadTahaNaveed commented Dec 27, 2024

ToLboo commented Dec 30, 2024

Mocuto commented Jan 6, 2025

ToLboo commented Jan 17, 2025

MuhammadTahaNaveed commented Jan 18, 2025

ToLboo commented Feb 21, 2025

jrgemignani commented Feb 21, 2025

When it executes VLE(Variable Length Edge) query, why it unable to find the data in the database. #2143

When it executes VLE(Variable Length Edge) query, why it unable to find the data in the database. #2143

Comments

ToLboo commented Dec 26, 2024

MuhammadTahaNaveed commented Dec 27, 2024

ToLboo commented Dec 30, 2024

Mocuto commented Jan 6, 2025

ToLboo commented Jan 17, 2025

MuhammadTahaNaveed commented Jan 18, 2025

ToLboo commented Feb 21, 2025

jrgemignani commented Feb 21, 2025