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

[Hyundong-Seo]

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

[jrgemignani]

Thank you for your question!

For any issues like this, please provide the following information to allow us to look into it -

1. The version of PostgreSQL you are using.
2. The version of AGE you are using.
3. The OS that you are using.
4. A simple explanation of the dataset being used.
5. A small sample of the dataset that causes the issue so that we can reproduce it.
6. An example showing the issue.
7. What your are trying to do.

Unfortunately, until I know more, I cannot give you an answer.

john

[Hyundong-Seo]

The information tested is as follows.

1. PG11.17
2. AGE - 1.1.0 / commit version - 4e9110d
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

[jrgemignani]

Does the command error if you don't use EXPLAIN at all?

[Hyundong-Seo]

@jrgemignani I get the same error, even if I don't use EXPLAIN

[jrgemignani]

@Hyundong-Seo, I am unable to reproduce your issue. So, I will need more information -

1. What is the dataset that you are using? How is it loaded? Where is it from?
2. Where is the v.id that you are looking for from?

For that id - WHERE v.id = '962072674361' - is that from the vertex's internal id or is it from some property that is stored and named id?

I should note that if it is from the vertex's id, you need to use the id() function -

psql-11.5-5432-pgsql=# SELECT * from cypher('graph1', $$ MATCH (v:comment)-[e:hascreatorcomment]->(v2:person)
  WHERE v.id = 844424930131969 RETURN v, v2 $$) as (v agtype, v2 agtype);
 v | v2
---+----
(0 rows)

psql-11.5-5432-pgsql=# SELECT * from cypher('graph1', $$ MATCH (v:comment)-[e:hascreatorcomment]->(v2:person)
  WHERE id(v) = 844424930131969 RETURN v, v2 $$) as (v agtype, v2 agtype);
                                   v                                   |                                  v2

-----------------------------------------------------------------------+------------------------------------------------------------
-----------
 {"id": 844424930131969, "label": "comment", "properties": {}}::vertex | {"id": 1407374883553281, "label": "person", "properties": {
}}::vertex
(1 row)

psql-11.5-5432-pgsql=#

[jrgemignani]

@Hyundong-Seo ?

[Hyundong-Seo]

@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

[jrgemignani]

That happens sometimes and it is hard to figure out why. If you happen to find something like this again, that is repeatable with a specific dataset, please let us know.

[avowkind]

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]

@avowkind What version of AGE are you using? What PG version?

[jrgemignani]

From the code it looks to only give that specific error if the start or end vertex for the edge don't exist at the time of the vle command execution.

[avowkind]

is it worth creating a new bug report?

Describe the bug
A clear and concise description of what the bug is.

It is possible to get the database into a state where a request of the form:

SELECT * FROM cypher('fishpond', $$
MATCH (p:PropertyType)<-[edge:typeOf*]-(node:PropertyType)
WHERE p.id = 'fish_weight_g'
RETURN node, edge
$$) as ( node agtype, edge agtype);

returns the error

ERROR,XX000,"insert_vertex_edge: failed to insert"

While the same request with [edge:typeOf] i.e no variable length path on the edge succeeds

However, a second attempt in the same session will succeed.

Is is an instance of #365 ( which was closed with insufficient information).

How are you accessing AGE (Command line, driver, etc.)?

• Can be reproduced in psql, PGAdmin and nodejs driver.

What data setup do we need to do?

• I'm not sure I can reproduce this issue in a small example as it only occurred after some time of use with a large dataset.
• however the same issue does appear in a db setup from a backup of the production db. i.e. I can provide an example if required although this is

What is the necessary configuration info needed?

• docker image: apache/age:PG15_latest

What is the command that caused the error?

SELECT * FROM cypher('fishpond', $$
MATCH (p:PropertyType)<-[edge:typeOf*]-(node:PropertyType)
WHERE p.id = 'fish_weight_g'
RETURN node, edge
$$) as ( node agtype, edge agtype);

ERROR,XX000,"insert_vertex_edge: failed to insert"

Expected behavior
on repeating the request we get back

"{""id"": 844424930131972, ""label"": ""PropertyType"", ""properties"": {""id"": ""average_fish_weight_g"", ""name"": ""Average fish weight"", ""published"": true, ""relations"": [{""id"": ""fish_weight_g"", ""relation"": ""typeOf""}], ""resultType"": ""measurement"", ""description"": ""The average weight of the fish in the tank""}}::vertex"	"{""id"": 1125899906842627, ""label"": ""typeOf"", ""end_id"": 844424930131971, ""start_id"": 844424930131972, ""properties"": {}}::edge"
"{""id"": 844424930131974, ""label"": ""PropertyType"", ""properties"": {""id"": ""culled_fish_weight_g"", ""name"": ""Fish Weight (culled)"", ""units"": ""g"", ""published"": true, ""relations"": [{""id"": ""fish_weight_g"", ""relation"": ""typeOf""}], ""resultType"": ""measurement"", ""description"": ""Weight of fish after culling""}}::vertex"	"{""id"": 1125899906842628, ""label"": ""typeOf"", ""end_id"": 844424930131971, ""start_id"": 844424930131974, ""properties"": {}}::edge"
"{""id"": 844424930131975, ""label"": ""PropertyType"", ""properties"": {""id"": ""mortality_fish_weight_g"", ""name"": ""Fish Weight (mortality)"", ""units"": ""g"", ""published"": true, ""relations"": [{""id"": ""fish_weight_g"", ""relation"": ""typeOf""}], ""resultType"": ""measurement"", ""description"": ""Weight of dead fish""}}::vertex"	"{""id"": 1125899906842629, ""label"": ""typeOf"", ""end_id"": 844424930131971, ""start_id"": 844424930131975, ""properties"": {}}::edge"
"{""id"": 844424930131976, ""label"": ""PropertyType"", ""properties"": {""id"": ""sampled_live_fish_weight_g"", ""name"": ""Fish Weight (sampled)"", ""units"": ""g"", ""published"": true, ""relations"": [{""id"": ""fish_weight_g"", ""relation"": ""typeOf""}], ""resultType"": ""measurement"", ""description"": ""Weight of live sampled fish""}}::vertex"	"{""id"": 1125899906842630, ""label"": ""typeOf"", ""end_id"": 844424930131971, ""start_id"": 844424930131976, ""properties"": {}}::edge"
"{""id"": 844424930131977, ""label"": ""PropertyType"", ""properties"": {""id"": ""morphometric_fish_weight_g"", ""name"": ""Fish Weight (morphometric)"", ""units"": ""g"", ""published"": true, ""relations"": [{""id"": ""fish_weight_g"", ""relation"": ""typeOf""}], ""resultType"": ""measurement"", ""description"": ""Weight of live fish in tank calculated from morphometric imaging""}}::vertex"	"{""id"": 1125899906842631, ""label"": ""typeOf"", ""end_id"": 844424930131971, ""start_id"": 844424930131977, ""properties"": {}}::edge"