Thread: Getting "ERROR: unrecognized node type: 444" while creating an AST
I'm running version 16. I'm working with a table structure as follows:
Table: MyTable
Columns:
- ColumnA (JSONB)
- ColumnB (TEXT)
- ColumnC (INT8)
- ColumnD (INT8)
The primary key is a composite of `ColumnB` + `ColumnC`.
I’m trying to create an AST representation of a query for the `INSERT` operation. The initial AST for a simple insert looks like this:
INSERT INTO <myTable> (ColumnA, ColumnB)
VALUES (<list of values>)
This structure works well for inserting data. However, I recently added another column (`ColumnD`), and the updated insert query is as follows:
INSERT INTO <myTable> (ColumnA, ColumnB, ColumnC, ColumnD)
VALUES (<list of values>)
ON CONFLICT (ColumnB, ColumnC)
DO UPDATE SET
ColumnA = EXCLUDED.ColumnA,
ColumnD = EXCLUDED.ColumnD
WHERE ColumnD < <timestampEpochInSeconds>
RETURNING ColumnA;
I’m trying to add the `ON CONFLICT` expression to the AST, but I’m encountering an "Unrecognized Node" error.
What I've tried
1. I’ve tried various ways to construct the struct, but the error persists.
2. Found this thread discussing the issue and followed the steps setting up verbose error logging and using gdb to print full backtrace.
https://www.postgresql.org/message-id/CADTdw-yGfNyHSLXajBFgCEod9dCh8Ys2%3D9j-gNh6kX5oEpkRNA%40mail.gmail.com
I found through logs that the error originates from line 2053 in `NodeFuncs.c`. I can’t correlate the unrecognized node to any enum value in `Nodes.h`. Any pointers on how to navigate that file would be greatly appreciated! The logs after setting error logging to verbose-
2024-10-01 06:38:34.040 UTC [96871] ERROR: XX000: unrecognized node type: 444
2024-10-01 06:38:34.040 UTC [96871] LOCATION: expression_tree_walker_impl, nodeFuncs.c:2539
The bt logs were not very useful since all variables were 'optimized out'.
Here's the code I used to construct the query:
Query *createInsertQuery() {
Query *query = makeNode(Query);
query->commandType = CMD_INSERT;
query->querySource = QSRC_ORIGINAL;
query->canSetTag = true;
/* Base table RTE */
RangeTblEntry *rte = makeNode(RangeTblEntry);
List *colNames = list_make4(makeString("colA"), makeString("colB"),
makeString("colC"), makeString("colD"));
rte->rtekind = RTE_RELATION;
rte->relid = myTable->relationId;
rte->alias = rte->eref = makeAlias("myTable", colNames);
rte->lateral = false;
rte->inFromCl = false;
rte->relkind = RELKIND_RELATION;
rte->functions = NIL;
rte->inh = true;
RTEPermissionInfo *permInfo = addRTEPermissionInfo(&query->rteperminfos, rte);
permInfo->requiredPerms = ACL_INSERT;
rte->rellockmode = RowExclusiveLock;
query->rtable = lappend(query->rtable, rte);
query->resultRelation = 1;
/* VALUES RTE */
List *valuesColNames = list_make4(makeString("colA"), makeString("colB"),
makeString("colC"), makeString("colD"));
RangeTblEntry *valuesRte = makeNode(RangeTblEntry);
valuesRte->rtekind = RTE_VALUES;
valuesRte->alias = valuesRte->eref = makeAlias("values", valuesColNames);
valuesRte->lateral = false;
valuesRte->inFromCl = false;
valuesRte->values_lists = list_make1(values);
valuesRte->inh = false;
valuesRte->inFromCl = true;
valuesRte->coltypes = list_make4_oid(JSONBOID, TEXTOID, INT8OID, INT8OID);
valuesRte->coltypmods = list_make4_int(-1, -1, -1, -1);
valuesRte->colcollations = list_make4_oid(InvalidOid, InvalidOid, InvalidOid, InvalidOid);
query->rtable = lappend(query->rtable, valuesRte);
RangeTblRef *valuesRteRef = makeNode(RangeTblRef);
valuesRteRef->rtindex = 2;
List *fromList = list_make1(valuesRteRef);
query->jointree = makeFromExpr(fromList, NULL);
/* Target list */
query->targetList = list_make4(
makeTargetEntry((Expr *) makeVar(2, 1, JSONBOID, -1, InvalidOid, 0), 1, "colA", false),
makeTargetEntry((Expr *) makeVar(2, 2, TEXTOID, -1, InvalidOid, 0), 2, "colB", false),
makeTargetEntry((Expr *) makeVar(2, 3, INT8OID, -1, InvalidOid, 0), 3, "colC", false),
makeTargetEntry((Expr *) makeVar(2, 4, INT8OID, -1, InvalidOid, 0), 4, "colD", false)
);
/* On Conflict */
OnConflictExpr *onConflict = makeNode(OnConflictExpr);
onConflict->action = ONCONFLICT_UPDATE;
int64 myTimestamp = 1672531199; // Example timestamp
ColumnRef *conflictColumnRef = makeNode(ColumnRef);
conflictColumnRef->fields = list_make1(makeString("colD"));
conflictColumnRef->location = -1;
A_Expr *onConflictWhereExpr = makeSimpleA_Expr(AEXPR_OP, "<", (Node *)conflictColumnRef,
(Node *)makeConst(INT8OID, -1, InvalidOid, sizeof(int64),
Int64GetDatum((int)myTimestamp), false, true), -1);
onConflict->onConflictWhere = (Node *)onConflictWhereExpr;
/* Primary key */
onConflict->arbiterElems = list_make2(makeString("colB"), makeString("colC"));
TargetEntry *updateTargetD = makeTargetEntry((Expr *) makeVar(2, 4, INT8OID, -1, InvalidOid, 0),
4, "colD", false);
TargetEntry *updateTargetA = makeTargetEntry((Expr *) makeVar(2, 1, JSONBOID, -1, InvalidOid, 0),
1, "colA", false);
/* Update target list */
onConflict->onConflictSet = list_make2(updateTargetA, updateTargetD);
query->onConflict = onConflict;
/* Returning list */
query->returningList = list_make1(
makeTargetEntry((Expr *) makeVar(1, 1, JSONBOID, -1, InvalidOid, 0),
1, "colA", false)
);
return query;
}
Amaan Haque <amaanhaque@microsoft.com> writes: > Im trying to create an AST representation of a query for the `INSERT` operation. ... > I found through logs that the error originates from line 2053 in `NodeFuncs.c`. I cant correlate the unrecognized nodeto any enum value in `Nodes.h`. Any pointers on how to navigate that file would be greatly appreciated! I think most of us rely on gdb these days. In community v16 I get: (gdb) p (NodeTag) 444 $1 = T_String You might have better luck identifying just where it's failing if you recompile nodeFuncs.c with -O0. The most obviously fishy thing I'm seeing in your code is > A_Expr *onConflictWhereExpr = makeSimpleA_Expr(AEXPR_OP, "<", (Node *)conflictColumnRef, > (Node *)makeConst(INT8OID, -1, InvalidOid, sizeof(int64), > Int64GetDatum((int)myTimestamp), false, true), -1); An A_Expr is only valid in raw_parser output, but what you seem to be trying to construct is a post-parse-analysis tree. regards, tom lane