age: [Bitnine Tech] - When it executes VLE(Variable Length Edge) query, why it occurs an error just the first try?
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)
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
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.
When I tried it again, but it was executed today. thank you for your reply
The information tested is as follows.
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