create … with … The execution plan of match is abnormal

[AnnXie2001]

Describe the bug
create … with … The execution plan of match is abnormal.

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

• Command line

What data setup do we need to do?
psql

CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT * FROM ag_catalog.create_graph('test_graph');

What is the command that caused the error?

SELECT * FROM cypher('test_graph', $$
CREATE
(a:Person {name: 'Jane', livesIn: 'London'}),
(b:Person {name: 'Tom', livesIn: 'Copenhagen'})
WITH a, b
MATCH (p:Person)
RETURN p.name
$$) AS (result agtype);

The first execution plan was different from the subsequent ones. The vertex was not created in the first attempt.

Expected behavior
The vertex creates in the first attempt.

Environment (please complete the following information):

• Version: postgresql 15.7
  age 1.5.0

Additional context
postgres=# SELECT * FROM ag_catalog.create_graph('test_graph');
EXPLAIN SELECT * FROM ag_catalog.cypher('test_graNOTICE: graph "test_graph" has been created
create_graph

(1 row)

ppostgres=# EXPLAIN SELECT * FROM ag_catalog.cypher('test_graph', $
postgres$# CREATE
postgres$# (a:Person {name: 'Jane', livesIn: 'London'}),
postgres$# (b:Person {name: 'Tom', livesIn: 'Copenhagen'})
postgres$# WITH a, b
postgres$# MATCH (p:Person)
postgres$# RETURN p.name
postgres$# $) AS (result ag_catalog.agtype);
QUERY PLAN

Result (cost=0.00..0.00 rows=0 width=32)
One-Time Filter: false
(2 rows)

postgres=# EXPLAIN SELECT * FROM ag_catalog.cypher('test_graph', $
CREATE
(a:Person {name: 'Jane', livesIn: 'London'}),
(b:Person {name: 'Tom', livesIn: 'Copenhagen'})
WITH a, b
MATCH (p:Person)
RETURN p.name
$) AS (result ag_catalog.agtype);
QUERY PLAN

Nested Loop (cost=0.00..43.00 rows=1200 width=32)
-> Custom Scan (Cypher Create) (cost=0.00..0.00 rows=0 width=0)
-> Subquery Scan on _age_default_alias_previous_cypher_clause (cost=0.00..0.03 rows=1 width=0)
-> Result (cost=0.00..0.02 rows=1 width=192)
-> Seq Scan on "Person" p (cost=0.00..22.00 rows=1200 width=40)
(5 rows)

The first time the stack of the plan is executed, ExecResult() directly returns empty:

#0 ExecResult (pstate=0x559cf111fd18) at nodeResult.c:77
#1 0x0000559ce43043de in ExecProcNodeFirst (node=0x559cf111fd18) at execProcnode.c:464
#2 0x0000559ce42f8ba3 in ExecProcNode (node=0x559cf111fd18) at ../../../src/include/executor/executor.h:262
#3 0x0000559ce42fb39f in ExecutePlan (estate=0x559cf111fab8, planstate=0x559cf111fd18, use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true,
numberTuples=0, direction=ForwardScanDirection, dest=0x559cf10218c0, execute_once=true) at execMain.c:1636
#4 0x0000559ce42f9121 in standard_ExecutorRun (queryDesc=0x559cf1009878, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:363
#5 0x0000559ce42f8fb1 in ExecutorRun (queryDesc=0x559cf1009878, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:307
#6 0x0000559ce452ff0c in PortalRunSelect (portal=0x559cf0f06ad8, forward=true, count=0, dest=0x559cf10218c0) at pquery.c:924
#7 0x0000559ce452fb7a in PortalRun (portal=0x559cf0f06ad8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x559cf10218c0, altdest=0x559cf10218c0,
qc=0x7ffd24d3f620) at pquery.c:768
#8 0x0000559ce4529376 in exec_simple_query (
query_string=0x559cf0e9b5c8 "SELECT * FROM ag_catalog.cypher('test_graph', $\n CREATE\n (a:Person {name: 'Jane', livesIn: 'London'}),\n (b:Person {name: 'Tom', livesIn: 'Copenhagen'})\n WITH a, b\n MATCH (p:Person)\n RETUR"...) at postgres.c:1253
#9 0x0000559ce452dce6 in PostgresMain (dbname=0x559cf0ec69a8 "postgres", username=0x559cf0e97c48 "gpadmin") at postgres.c:4615
#10 0x0000559ce446f6bc in BackendRun (port=0x559cf0ebc2e0) at postmaster.c:4528
#11 0x0000559ce446efa1 in BackendStartup (port=0x559cf0ebc2e0) at postmaster.c:4256
#12 0x0000559ce446b52a in ServerLoop () at postmaster.c:1823
#13 0x0000559ce446accd in PostmasterMain (argc=3, argv=0x559cf0e95b90) at postmaster.c:1495
#14 0x0000559ce4374db4 in main (argc=3, argv=0x559cf0e95b90) at main.c:213

The stack of the second execution plan enters the function for creating vertex:

#0 create_vertex (css=0x559cf1006498, node=0x559cf10067b8, next=0x0, list=0x559cf1006c38) at src/backend/executor/cypher_create.c:498
#1 0x00007f2f500ae521 in process_pattern (css=0x559cf1006498) at src/backend/executor/cypher_create.c:158
#2 0x00007f2f500ae6f4 in exec_cypher_create (node=0x559cf1006498) at src/backend/executor/cypher_create.c:217
#3 0x0000559ce431f86a in ExecCustomScan (pstate=0x559cf1006498) at nodeCustom.c:115
#4 0x0000559ce43043de in ExecProcNodeFirst (node=0x559cf1006498) at execProcnode.c:464
#5 0x0000559ce4341598 in ExecProcNode (node=0x559cf1006498) at ../../../src/include/executor/executor.h:262
#6 0x0000559ce43417b6 in ExecNestLoop (pstate=0x559cf10062a8) at nodeNestloop.c:109
#7 0x0000559ce43043de in ExecProcNodeFirst (node=0x559cf10062a8) at execProcnode.c:464
#8 0x0000559ce42f8ba3 in ExecProcNode (node=0x559cf10062a8) at ../../../src/include/executor/executor.h:262
#9 0x0000559ce42fb39f in ExecutePlan (estate=0x559cf1006008, planstate=0x559cf10062a8, use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true,
numberTuples=0, direction=ForwardScanDirection, dest=0x559cf1012c08, execute_once=true) at execMain.c:1636
#10 0x0000559ce42f9121 in standard_ExecutorRun (queryDesc=0x559cf0f58be8, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:363
#11 0x0000559ce42f8fb1 in ExecutorRun (queryDesc=0x559cf0f58be8, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:307
#12 0x0000559ce452ff0c in PortalRunSelect (portal=0x559cf0f06ad8, forward=true, count=0, dest=0x559cf1012c08) at pquery.c:924
#13 0x0000559ce452fb7a in PortalRun (portal=0x559cf0f06ad8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x559cf1012c08, altdest=0x559cf1012c08,
qc=0x7ffd24d3f620) at pquery.c:768
#14 0x0000559ce4529376 in exec_simple_query (
query_string=0x559cf0e9b5c8 "SELECT * FROM ag_catalog.cypher('test_graph', $\n CREATE\n (a:Person {name: 'Jane', livesIn: 'London'}),\n (b:Person {name: 'Tom', livesIn: 'Copenhagen'})\n WITH a, b\n MATCH (p:Person)\n RETUR"...) at postgres.c:1253
#15 0x0000559ce452dce6 in PostgresMain (dbname=0x559cf0ec69a8 "postgres", username=0x559cf0e97c48 "gpadmin") at postgres.c:4615
#16 0x0000559ce446f6bc in BackendRun (port=0x559cf0ebc2e0) at postmaster.c:4528
#17 0x0000559ce446efa1 in BackendStartup (port=0x559cf0ebc2e0) at postmaster.c:4256
#18 0x0000559ce446b52a in ServerLoop () at postmaster.c:1823
#19 0x0000559ce446accd in PostmasterMain (argc=3, argv=0x559cf0e95b90) at postmaster.c:1495
#20 0x0000559ce4374db4 in main (argc=3, argv=0x559cf0e95b90) at main.c:213

[jrgemignani]

@AnnXie2001 Verified in the master. It seems to happen with this command to any new label. While the command does not execute correctly the first time, it does create the new label which allows the command to execute correctly the second time -

psql-16.2-5432-pgsql=# SELECT * FROM ag_catalog.create_graph('issue_2193');
NOTICE:  graph "issue_2193" has been created
 create_graph
--------------

(1 row)

psql-16.2-5432-pgsql=# select * from cypher('issue_2193', $$ match (u) return u $$) as (result agtype);
 result
--------
(0 rows)

psql-16.2-5432-pgsql=# select * from ag_label where relation::text like '%issue%';
       name       | graph | id | kind |          relation           |        seq_name
------------------+-------+----+------+-----------------------------+-------------------------
 _ag_label_vertex | 17181 |  1 | v    | issue_2193._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 17181 |  2 | e    | issue_2193._ag_label_edge   | _ag_label_edge_id_seq
(2 rows)

psql-16.2-5432-pgsql=# SELECT * FROM cypher('issue_2193', $$
CREATE (a:abc {name: 'Jane', livesIn: 'London'}), (b:abc {name: 'Tom', livesIn: 'Copenhagen'})
WITH a, b MATCH (p:abc) RETURN p.name $$) AS (result agtype);
 result
--------
(0 rows)

psql-16.2-5432-pgsql=# SELECT * FROM cypher('issue_2193', $$
CREATE (a:def {name: 'Jane', livesIn: 'London'}), (b:def {name: 'Tom', livesIn: 'Copenhagen'})
WITH a, b MATCH (p:def) RETURN p.name $$) AS (result agtype);
 result
--------
(0 rows)

psql-16.2-5432-pgsql=# select * from ag_label where relation::text like '%issue%';
       name       | graph | id | kind |          relation           |        seq_name
------------------+-------+----+------+-----------------------------+-------------------------
 _ag_label_vertex | 17181 |  1 | v    | issue_2193._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 17181 |  2 | e    | issue_2193._ag_label_edge   | _ag_label_edge_id_seq
 abc              | 17181 |  3 | v    | issue_2193.abc              | abc_id_seq
 def              | 17181 |  4 | v    | issue_2193.def              | def_id_seq
(4 rows)

psql-16.2-5432-pgsql=# select create_vlabel('issue_2193','ghi');
NOTICE:  VLabel "ghi" has been created
 create_vlabel
---------------

(1 row)

psql-16.2-5432-pgsql=# SELECT * FROM cypher('issue_2193', $$
CREATE (a:ghi {name: 'Jane', livesIn: 'London'}), (b:ghi {name: 'Tom', livesIn: 'Copenhagen'})
WITH a, b MATCH (p:ghi) RETURN p.name $$) AS (result agtype);
 result
--------
 "Jane"
 "Tom"
(2 rows)

psql-16.2-5432-pgsql=#

A temporary fix, while a resolution is worked on, is to create the label in advance.

[AnnXie2001]

In my inspection, I found that if it is the first creation, the execution plan of this statement will perform a join on an empty table, resulting in the return being null. I wonder if this issue will be fixed later?

[jrgemignani]

@AnnXie2001 It will be looked into. Until it can be fixed, though, please use the temporary fix.

[gregfelice]

I've submitted a fix for this in PR #2341.

Root cause: match_check_valid_label() in transform_cypher_match() runs before transform_prev_cypher_clause() processes the predecessor chain. Since CREATE's transform creates the label table as a side effect, the Person label is not yet in the cache when the check runs. The check generates a One-Time Filter: false plan that returns 0 rows.

On the second execution, the label table already exists from the first run, so the check passes and produces the correct Seq Scan plan.

Fix: Defer the label validity check to after transform_prev_cypher_clause() when the predecessor chain contains a DML operation (CREATE, SET, DELETE, MERGE).

[jrgemignani]

@gregfelice Ty for the help here! We've been so busy on other tasks this one almost slipped by.

[gregfelice]

My pleasure - thank you for your patience as I learn the process.

…

On Fri, Feb 27, 2026 at 12:11 PM John Gemignani ***@***.***> wrote: *jrgemignani* left a comment (apache/age#2193) <#2193 (comment)> @gregfelice <https://github.com/gregfelice> Ty for the help here! We've been so busy on other tasks this one almost slipped by. — Reply to this email directly, view it on GitHub <#2193 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABBXGF3OC4VHKXP4SJ4YZT4OB3CTAVCNFSM6AAAAACWABWJECVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTSNZUGA3DMOJQGI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Greg Felice 415.343.5227 ***@***.*** linkedin.com/in/gregfelice calendly.com/gregfelice/30min