Currently I am working on an issue that happens on PostgreSQL server version 16 and does not happen with the 15 version (alongside Apache AGE).
The issue happens when executing the OPTIONAL MATCH query which lead to a LEFT-JOIN
Both queries have the same PLAN but they does not execute the same stack trace
The query is as the following:
SELECT * FROM cypher('cypher_match', $$
MATCH (u:opt_match_v)
OPTIONAL MATCH (u)-[m]-(l)
RETURN u.name as u, type(m), l.name as l
$$) AS (u agtype, m agtype, l agtype);
Adding EXPLAIN before MATCH to get the query plan, returns at both versions almost the same with changes in the ids of the label ids and etc. but they are the same steps
Nested Loop Left Join (cost=0.00..73536354.30 rows=69969 width=96)
Join Filter: (((m.start_id = (age_id(_agtype_build_vertex(u.id, _label_name('124289'::oid, u.id), u.properties)))::graphid) AND (m.end_i
d = l.id)) OR ((m.end_id = (age_id(_agtype_build_vertex(u.id, _label_name('124289'::oid, u.id), u.properties)))::graphid) AND (m.start_id =
l.id)))
-> Seq Scan on opt_match_v u (cost=0.00..22.00 rows=1200 width=40)
-> Nested Loop (cost=0.00..14632.13 rows=1166171 width=96)
-> Append (cost=0.00..28.00 rows=1201 width=40)
-> Seq Scan on _ag_label_vertex l_1 (cost=0.00..0.00 rows=1 width=40)
-> Seq Scan on opt_match_v l_2 (cost=0.00..22.00 rows=1200 width=40)
-> Materialize (cost=0.00..29.41 rows=971 width=56)
-> Append (cost=0.00..24.56 rows=971 width=56)
-> Seq Scan on _ag_label_edge m_1 (cost=0.00..0.00 rows=1 width=56)
-> Seq Scan on opt_match_e m_2 (cost=0.00..19.70 rows=970 width=56)
(11 rows)
Given a break-point at _label_name function which has the problem that throws an error on pg16
At first entry of
The stack trace of PG15
age.so!_label_name(FunctionCallInfo fcinfo) (age-16/src/backend/catalog/ag_label.c:190)
ExecInterpExpr(ExprState * state, ExprContext * econtext, _Bool * isnull) (postgresql-15.3/src/backend/executor/execExprInterp.c:1262)
ExecEvalExprSwitchContext(_Bool * isNull, ExprContext * econtext, ExprState * state) (postgresql-15.3/src/include/executor/executor.h:341)
ExecProject(ProjectionInfo * projInfo) (postgresql-15.3/src/include/executor/executor.h:375)
ExecScan(ScanState * node, ExecScanAccessMtd accessMtd, ExecScanRecheckMtd recheckMtd) (postgresql-15.3/src/backend/executor/execScan.c:238)
ExecProcNode(PlanState * node) (postgresql-15.3/src/include/executor/executor.h:259)
ExecAppend(PlanState * pstate) (postgresql-15.3/src/backend/executor/nodeAppend.c:347)
ExecProcNode(PlanState * node) (postgresql-15.3/src/include/executor/executor.h:259)
ExecNestLoop(PlanState * pstate) (postgresql-15.3/src/backend/executor/nodeNestloop.c:109)
ExecProcNode(PlanState * node) (postgresql-15.3/src/include/executor/executor.h:259)
ExecNestLoop(PlanState * pstate) (postgresql-15.3/src/backend/executor/nodeNestloop.c:160)
ExecProcNode(PlanState * node) (postgresql-15.3/src/include/executor/executor.h:259)
ExecutePlan(_Bool execute_once, DestReceiver * dest, ScanDirection direction, uint64 numberTuples, CmdType operation, _Bool use_parallel_mode, PlanState * planstate, EState * estate) (postgresql-15.3/src/backend/executor/execMain.c:1636)
standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) (postgresql-15.3/src/backend/executor/execMain.c:363)
PortalRunSelect(Portal portal, _Bool forward, long count, DestReceiver * dest) (postgresql-15.3/src/backend/tcop/pquery.c:924)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) (postgresql-15.3/src/backend/tcop/pquery.c:768)
exec_simple_query(const char * query_string) (postgresql-15.3/src/backend/tcop/postgres.c:1250)
PostgresMain(const char * dbname, const char * username) (postgresql-15.3/src/backend/tcop/postgres.c:4593)
BackendRun(Port * port) (postgresql-15.3/src/backend/postmaster/postmaster.c:4511)
BackendStartup(Port * port) (postgresql-15.3/src/backend/postmaster/postmaster.c:4239)
ServerLoop() (postgresql-15.3/src/backend/postmaster/postmaster.c:1806)
PostmasterMain(int argc, char ** argv) (postgresql-15.3/src/backend/postmaster/postmaster.c:1478)
main(int argc, char ** argv) (postgresql-15.3/src/backend/main/main.c:202)
The stack trace of PG16
age.so!_label_name(FunctionCallInfo fcinfo) (age-6/src/backend/catalog/ag_label.c:190)
ExecInterpExpr(ExprState * state, ExprContext * econtext, _Bool * isnull) postgresql-16/src/backend/executor/execExprInterp.c:734)
ExecEvalExprSwitchContext(_Bool * isNull, ExprContext * econtext, ExprState * state) postgresql-16/src/include/executor/executor.h:355)
ExecQual(ExprContext * econtext, ExprState * state) postgresql-16/src/include/executor/executor.h:424)
ExecNestLoop(PlanState * pstate) postgresql-16/src/backend/executor/nodeNestloop.c:214)
ExecProcNode(PlanState * node) postgresql-16/src/include/executor/executor.h:273)
ExecutePlan(_Bool execute_once, DestReceiver * dest, ScanDirection direction, uint64 numberTuples, CmdType operation, _Bool use_parallel_mode, PlanState * planstate, EState * estate) postgresql-16/src/backend/executor/execMain.c:1661)
standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) postgresql-16/src/backend/executor/execMain.c:365)
PortalRunSelect(Portal portal, _Bool forward, long count, DestReceiver * dest) postgresql-16/src/backend/tcop/pquery.c:924)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) postgresql-16/src/backend/tcop/pquery.c:768)
exec_simple_query(const char * query_string) postgresql-16/src/backend/tcop/postgres.c:1274)
PostgresMain(const char * dbname, const char * username) postgresql-16/src/backend/tcop/postgres.c:4632)
BackendRun(Port * port) postgresql-16/src/backend/postmaster/postmaster.c:4461)
BackendStartup(Port * port) postgresql-16/src/backend/postmaster/postmaster.c:4189)
ServerLoop() postgresql-16/src/backend/postmaster/postmaster.c:1779)
PostmasterMain(int argc, char ** argv) postgresql-16/src/backend/postmaster/postmaster.c:1463)
main(int argc, char ** argv) postgresql-16/src/backend/main/main.c:198)
As we see they are different starting from the call ExecutePlan
At ExecNestLoop
The following ExecProcNode
at line 160 is being called in pg15 while in pg16 it does not
innerTupleSlot = ExecProcNode(innerPlan);
What makes the stack-trace of the same query plan is different?
References:
Reproduce
- Assumes having installed PostgreSQL and Apache AGE
LOAD 'age';
SET search_path TO ag_catalog;
SELECT create_graph('cypher_match');
SELECT * FROM cypher('cypher_match', $$
CREATE (:opt_match_v {name: 'someone'})-[:opt_match_e]->(:opt_match_v {name: 'somebody'}),
(:opt_match_v {name: 'anybody'})-[:opt_match_e]->(:opt_match_v {name: 'nobody'})
$$) AS (u agtype);
SELECT * FROM cypher('cypher_match', $$
EXPLAIN MATCH (u:opt_match_v)
OPTIONAL MATCH (u)-[m]-(l)
RETURN u.name as u, type(m), l.name as l
$$) AS (u agtype, m agtype, l agtype);
Results of pg15:
u | m | l
------------+---------------+------------
"someone" | "opt_match_e" | "somebody"
"somebody" | "opt_match_e" | "someone"
"anybody" | "opt_match_e" | "nobody"
"nobody" | "opt_match_e" | "anybody"
(4 rows)
Results of pg16:
ERROR: graph_oid and label_id must not be null
2
Answers
The difference between stacktraces of PG 15 and 16 is due to the internal changes in query execution.
The error is telling that there is an issue with
graph_oid
andlabel_id
in PG 16 because they are null, but this issue was not in PG 15.So you need to check for AGE modifications and difference between source code of PG 15 and 16
The issue could be with some of the data types that might hav ebeen change in version AGE16 as compared to AGE15. Thats why AGE is not able to set the
label_oid
andgraph_oid
properly and its showing itnull
. The issue has been resolved now.