age: [Bitnine Tech] - When it executes VLE(Variable Length Edge) query, why it occurs an error just the first try?

vle_execute_error The first time I try the VLE query, I always get an insert_vertex_edge error. A second attempt at the same query executes the query.

ldbc=# SELECT * from cypher('test', $$
  EXPLAIN ANALYZE
  MATCH (v:comment)-[e:hascreatorcomment*1..3]->(v2:person)
  WHERE v.id = '962072674361'
  RETURN v, v2
$$) as (v agtype, v2 agtype);
ERROR:  insert_vertex_edge: failed to insert

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 36 (23 by maintainers)

Most upvoted comments

When run for the first time in the session I get a result of ‘false’. The following query still fails.

in fact it ensures that the script fails every time instead of just the first time.

This query

SELECT * FROM cypher(‘fishpond’, $$ MATCH ()-[edge:featureOfInterest]->() where id(edge) = 3377699720847780 RETURN edge ORDER BY id(edge) $$) as (edge agtype);

Returns null But this

SELECT * FROM fishpond._ag_label_edge e WHERE id::text = '3377699720847780’;

Return s "3377699720847780"“2533274790715812"“2251799813685250"”{}”

Where vertex 2533274790715812 does not exist.

So the above delete also doesn’t work.

Here is SQL to list dangling edges SELECT e.* FROM fishpond._ag_label_edge e LEFT JOIN fishpond._ag_label_vertex v ON e.start_id = v.id WHERE v.id IS NULL;

Let me know if you think this is right. - I get one result - the known issue.

So I then run DELETE FROM fishpond._ag_label_edge e WHERE id::text = '3377699720847780’;

Removing the line from the edge table.

And this fixes everything. The VLE search now runs ok.

So to sum up.

We have a dangling edge in ._ag_label_edge where the start_id does not exist or was somehow removed from the vertices table.

The check for dangling edges is

Here is SQL to list dangling edges SELECT e.* FROM fishpond._ag_label_edge e LEFT JOIN fishpond._ag_label_vertex v ON e.start_id = v.id WHERE v.id IS NULL;

And then you can delete an edge using DELETE FROM fishpond._ag_label_edge e WHERE id::text = ‘{id}’

I had to cast the id from a graphID.

The underlying question is whether a query of the form MATCH. x pattern DELETE x

Without the DETACH leaves dangling edges by design.

Thanks Andrew

On 11 Apr 2024, at 11:36 AM, John Gemignani @.***> wrote:

Only the end vertex for the edge exists.

fishpond=# SELECT * FROM cypher(‘fishpond’, $$ MATCH (u) WHERE id(u) = 2533274790715812 RETURN u $$) as (u agtype); u

(0 rows)

fishpond=# SELECT * FROM cypher(‘fishpond’, $$ MATCH (u) WHERE id(u) = 1407374883553780 RETURN u $$) as (u agtype); u



{“id”: 1407374883553780, “label”: “Feature”, “properties”: {“id”: “pfr:nelson:tank:C09”, “name”: “C09”, “relations”: [{“id”: “pfr:n elson:tank_group:C”, “relation”: “partOf”}], “description”: “C09”, “featureGroup”: “pfr:fg:tank”}}::vertex (1 row)

fishpond=#

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: @.***>

I can provide the database backup (its 18mb) but its not an especially complicated model.

I know this is closed. but I am getting the same issue.

After a long time of having no problem with VLE requests just today - on one instance of my database and not on another a request with * on the edge request causes the ERROR: “insert_vertex_edge: failed to insert” to appear.

This occurs on a variety of requests that usually work just fine.

I have found that if I establish an ongoing session e.g. using psql that I get this error the first time the request is run. Subsequent runs give the correct result without an error.

Requests going in through my App API always fail because each is run in a new session.

Why would running the query twice fix the problem?

@jrgemignani I’m sorry to response too late.

  1. What is the dataset that you are using? How is it loaded? Where is it from? -> This is the csv file used when using agensgraph.
  2. Where is the v.id that you are looking for from? -> it is from properties not from the vertex’s internal id, like this {“id”: 845387002806329, “label”: “comment”, “properties”: {“id”: “962072674361”, “id”: 962072674361, “length”: “83”, “content”: “About Bertolt Brecht rn; 10 FebruarAbout France Pierre-Louis About Danc ing on My”, “locationIP”: “213.55.127.9”, “browserUsed”: “Internet Explorer”, “creationDate”: “2011-04-14T05:20:46.802+0000”}}

When I tried it again, but it was executed today. thank you for your reply

The information tested is as follows.

  1. PG11.17
  2. AGE - 1.1.0 / commit version - 4e9110d0b88812b08c316ef7df2ff07f98e3a066
  3. Ubuntu 20.04.5 LTS
  4. The start vertex is about COMMENT, the end vertex is about PERSON and the edge is about simple relation between comment and person.
  5. I don’t know whether you want to see a dataset like this or other type. Edges greater than 2 depths may or may not exist. [start vertex - comment] { “id”: 845661880713217, “label”: “comment”, “properties”: { “id”: “1236950581249”, “id”: 1236950581249, “length”: “3”, “content”: “yes”, “locationIP”: “92.39.58.88”, “browserUsed”: “Chrome”, “creationDate”: “2011-08-17T14:26:59.961+0000” } } [edge - hascreatorcomment] { “id”: 3940649673949185, “label”: “hascreatorcomment”, “end_id”: 1981319953259400, “start_id”: 845661880713217, “properties”: {} } [end vertex - person] { “id”: 1981319953259400, “label”: “person”, “properties”: { “id”: “10995116284808”, “id”: 10995116284808, “gender”: “male”, “birthday”: “1982-02-04”, “lastName”: “Condariuc”, “firstName”: “Andrei”, “locationIP”: “92.39.58.88”, “browserUsed”: “Chrome”, “creationDate”: “2010-12-26T14:40:36.649+0000” } }
  6. I just executed it because I wanted to see the plan of the VLE lookup query, but an error occurred. But if I execute directly the same query again, it works. [the first try] ldbc=# SELECT * from cypher(‘test’, $$ ldbc$# EXPLAIN ANALYZE ldbc$# MATCH (v:comment)-[e:hascreatorcomment1…3]->(v2:person) ldbc$# WHERE v.id = ‘962072674361’ ldbc$# RETURN v, v2 ldbc$# $$) as (v agtype, v2 agtype); ERROR: insert_vertex_edge: failed to insert [The second try] ldbc=# SELECT * from cypher(‘test’, $$ EXPLAIN ANALYZE MATCH (v:comment)-[e:hascreatorcomment1…3]->(v2:person) WHERE v.id = ‘962072674361’ RETURN v, v2 $$) as (v agtype, v2 agtype); QUERY PLAN


Nested Loop (cost=0.01…1860999881.03 rows=33830636617 width=64) (actual time=3.128…9990.038 rows=1 loops=1) Join Filter: age_match_vle_terminal_edge(v.id, v2.id, _age_default_alias_0.edges) Rows Removed by Join Filter: 9891 -> Nested Loop (cost=0.01…314225.21 rows=10260000 width=267) (actual time=1.212…9976.277 rows=1 loops=1) -> Seq Scan on comment v (cost=0.00…109025.20 rows=10260 width=235) (actual time=0.420…9975.479 rows=1 loops=1) Filter: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name(‘41527’::oid, id), properties), ‘“id”’::agtype]) = ‘“962072674361”’ ::agtype) Rows Removed by Filter: 2052168 -> Function Scan on age_vle _age_default_alias_0 (cost=0.01…10.01 rows=1000 width=32) (actual time=0.785…0.787 rows=1 loops=1) -> Materialize (cost=0.00…514.38 rows=9892 width=263) (actual time=0.014…10.410 rows=9892 loops=1) -> Seq Scan on person v2 (cost=0.00…464.92 rows=9892 width=263) (actual time=0.009…1.965 rows=9892 loops=1) Planning Time: 0.594 ms Execution Time: 9991.038 ms (12 rows) 7. I have already checked by executing the query what I want to do, but I wonder why the error occurs only on the first try.

I hope this answer has been sufficient.

Hyundong