agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedCREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
8+ messages / 2 participants
[nested] [flat]
* CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-15 13:05 Maaz Syed Adeeb <maaz.adeeb@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Maaz Syed Adeeb @ 2026-07-15 13:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: matthew.ripley28@gmail.com; tgl@sss.pgh.pa.us
PostgreSQL version: 20devel (git master)
On current master, creating an index with an expression in an INCLUDE
(non-key) column fails with an internal error ("unrecognized node
type", SQLSTATE XX000) instead of the user-facing
FEATURE_NOT_SUPPORTED (0A000) "expressions are not supported in
included columns". The statement is still correctly rejected, but with
the wrong error class and a message.
Minimal repro
CREATE TABLE foo (id int PRIMARY KEY, x int, y int);
CREATE INDEX idx_foo ON foo (x) INCLUDE ((x + y));
Actual result on master (20devel):
postgres=# select version();
version
------------------------------------------------------------------------
PostgreSQL 20devel on aarch64-unknown-linux-gnu, compiled by gcc
(GCC) 7.3.1 20180712 (Red Hat 7.3.1-18), 64-bit
(1 row)
postgres=# CREATE TABLE foo (id int PRIMARY KEY, x int, y int);
CREATE TABLE
postgres=# CREATE INDEX idx_foo ON foo (x) INCLUDE ((x + y));
ERROR: unrecognized node type: 74
postgres=# \errverbose
ERROR: XX000: unrecognized node type: 74
LOCATION: expression_tree_walker_impl, nodeFuncs.c:2733
(built from git master, HEAD at the time of testing:
572c3b2ddf8c90303d57bf33add4dcbf1b30b866)
Expected result (behavior on all released majors, e.g. 16.13):
postgres=# CREATE INDEX idx_foo ON foo (x) INCLUDE ((x + y));
ERROR: expressions are not supported in included columns
postgres=# \errverbose
ERROR: 0A000: expressions are not supported in included columns
LOCATION: ComputeIndexAttrs, indexcmds.c:1910
This appears to have been introduced by:
commit 181b6185c79e09e6ac94428189d9afac807244ac
"Improve the names generated for indexes on expressions"
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=181b6185c79e09e6ac94428189d9afac80724...
which replaced the previous static "expr" fallback name with a walk of
the expression. Prior to that commit the INCLUDE expression was never
walked at this stage.
The trigger is any parenthesized expression in INCLUDE, not just
arithmetic; e.g. INCLUDE ((x)) fails the same way with "unrecognized
node type: 72"
(CC'ing Tom Lane, author of the patch that I've linked which is likely
causing the issue, and Matthew Ripley, author of a test that led me
down to this discovery when run against master)
Regards,
Maaz Syed Adeeb
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-15 18:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maaz Syed Adeeb <maaz.adeeb@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2026-07-15 18:29 UTC (permalink / raw)
To: Maaz Syed Adeeb <maaz.adeeb@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; matthew.ripley28@gmail.com
Maaz Syed Adeeb <maaz.adeeb@gmail.com> writes:
> On current master, creating an index with an expression in an INCLUDE
> (non-key) column fails with an internal error ("unrecognized node
> type", SQLSTATE XX000) instead of the user-facing
> FEATURE_NOT_SUPPORTED (0A000) "expressions are not supported in
> included columns". The statement is still correctly rejected, but with
> the wrong error class and a message.
Thanks for the report! This is evidently happening because we have
not applied parse transformation to the included columns. I think
that the most appropriate way to fix it is to start doing so, even
though the feature will be rejected later. More or less as attached
(but we ought to add a test case too).
regards, tom lane
Attachments:
[text/x-diff] wip-fix-index-included-expressions.patch (985B, ../../3574719.1784140144@sss.pgh.pa.us/2-wip-fix-index-included-expressions.patch)
download | inline diff:
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 58ccc7b68f2..ccf6ee55310 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3122,6 +3122,26 @@ transformIndexStmt(Oid relid, IndexStmt *stmt, const char *queryString)
}
}
+ /*
+ * Likewise take care of any expressions in INCLUDING. (At this writing,
+ * those will be rejected later on, but probably someday we'll wish to
+ * support them.)
+ */
+ foreach(l, stmt->indexIncludingParams)
+ {
+ IndexElem *ielem = (IndexElem *) lfirst(l);
+
+ if (ielem->expr)
+ {
+ /* Do parse transformation of the expression */
+ ielem->expr = transformExpr(pstate, ielem->expr,
+ EXPR_KIND_INDEX_EXPRESSION);
+
+ /* We have to fix its collations too */
+ assign_expr_collations(pstate, ielem->expr);
+ }
+ }
+
/*
* Check that only the base rel is mentioned. (This should be dead code
* now that add_missing_from is history.)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-15 18:57 Maaz Syed Adeeb <maaz.adeeb@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Maaz Syed Adeeb @ 2026-07-15 18:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org; matthew.ripley28@gmail.com
I'm happy to add a test case to the patch and resubmit it. It'd be my
first contribution to PG, so happy to get my feet wet with setting up
the env, running tests, contribution process, etc. Let me know if
that'd be OK.
Regards,
Maaz Syed Adeeb
On Wed, Jul 15, 2026 at 11:29 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Maaz Syed Adeeb <maaz.adeeb@gmail.com> writes:
> > On current master, creating an index with an expression in an INCLUDE
> > (non-key) column fails with an internal error ("unrecognized node
> > type", SQLSTATE XX000) instead of the user-facing
> > FEATURE_NOT_SUPPORTED (0A000) "expressions are not supported in
> > included columns". The statement is still correctly rejected, but with
> > the wrong error class and a message.
>
> Thanks for the report! This is evidently happening because we have
> not applied parse transformation to the included columns. I think
> that the most appropriate way to fix it is to start doing so, even
> though the feature will be rejected later. More or less as attached
> (but we ought to add a test case too).
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-15 19:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maaz Syed Adeeb <maaz.adeeb@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2026-07-15 19:12 UTC (permalink / raw)
To: Maaz Syed Adeeb <maaz.adeeb@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; matthew.ripley28@gmail.com
Maaz Syed Adeeb <maaz.adeeb@gmail.com> writes:
> I'm happy to add a test case to the patch and resubmit it. It'd be my
> first contribution to PG, so happy to get my feet wet with setting up
> the env, running tests, contribution process, etc. Let me know if
> that'd be OK.
Sure, happy to let you take a shot at it.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-16 05:20 Maaz Syed Adeeb <maaz.adeeb@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Maaz Syed Adeeb @ 2026-07-16 05:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org; matthew.ripley28@gmail.com
Attaching a patch with two regress tests. Let me know if I need to
change anything.
Regards,
Maaz Syed Adeeb
On Wed, Jul 15, 2026 at 12:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Maaz Syed Adeeb <maaz.adeeb@gmail.com> writes:
> > I'm happy to add a test case to the patch and resubmit it. It'd be my
> > first contribution to PG, so happy to get my feet wet with setting up
> > the env, running tests, contribution process, etc. Let me know if
> > that'd be OK.
>
> Sure, happy to let you take a shot at it.
>
> regards, tom lane
Attachments:
[application/octet-stream] v1-0001-Apply-parse-transformation-to-expressions-in-INCL.patch (3.4K, ../../CAG+FJqMUxTXMhFrShEfygnONTS-_yBJYwabwWS0G26hNDfv+DA@mail.gmail.com/2-v1-0001-Apply-parse-transformation-to-expressions-in-INCL.patch)
download | inline diff:
From 5b45b7946a4515229d8e32d3882ac5a00f839d28 Mon Sep 17 00:00:00 2001
From: Maaz Syed Adeeb <maaz.adeeb@gmail.com>
Date: Thu, 16 Jul 2026 05:08:07 +0000
Subject: [PATCH v1] Apply parse transformation to expressions in INCLUDE
columns
CREATE INDEX ... INCLUDE ((expr)) failed with an "unrecognized node type
error" instead of "expressions are not supported in included
columns". This was because we didn't run parse analysis on the included
columns, and started walking them as part of change to improve names for
expression indexes (181b6185)
Now, we transform the INCLUDE expressions just as we do the key-column
expressions. This gets rejected downstream with the right error message.
---
src/backend/parser/parse_utilcmd.c | 20 +++++++++++++++++++
src/test/regress/expected/index_including.out | 14 +++++++++++++
src/test/regress/sql/index_including.sql | 9 +++++++++
3 files changed, 43 insertions(+)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 58ccc7b..ccf6ee5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3122,6 +3122,26 @@ transformIndexStmt(Oid relid, IndexStmt *stmt, const char *queryString)
}
}
+ /*
+ * Likewise take care of any expressions in INCLUDING. (At this writing,
+ * those will be rejected later on, but probably someday we'll wish to
+ * support them.)
+ */
+ foreach(l, stmt->indexIncludingParams)
+ {
+ IndexElem *ielem = (IndexElem *) lfirst(l);
+
+ if (ielem->expr)
+ {
+ /* Do parse transformation of the expression */
+ ielem->expr = transformExpr(pstate, ielem->expr,
+ EXPR_KIND_INDEX_EXPRESSION);
+
+ /* We have to fix its collations too */
+ assign_expr_collations(pstate, ielem->expr);
+ }
+ }
+
/*
* Check that only the base rel is mentioned. (This should be dead code
* now that add_missing_from is history.)
diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out
index 4e8fe49..1c0b895 100644
--- a/src/test/regress/expected/index_including.out
+++ b/src/test/regress/expected/index_including.out
@@ -423,3 +423,17 @@ SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1;
RESET enable_seqscan;
DROP TABLE nametbl;
+/*
+ * 11. Expressions are not supported in included columns. Verify that we
+ * report a "feature not supported" error.
+ */
+CREATE TABLE tbl (c1 int, c2 int, c3 int);
+CREATE INDEX ON tbl (c1) INCLUDE ((c2 + c3));
+ERROR: expressions are not supported in included columns
+LINE 1: CREATE INDEX ON tbl (c1) INCLUDE ((c2 + c3));
+ ^
+CREATE INDEX ON tbl (c1) INCLUDE ((c2));
+ERROR: expressions are not supported in included columns
+LINE 1: CREATE INDEX ON tbl (c1) INCLUDE ((c2));
+ ^
+DROP TABLE tbl;
diff --git a/src/test/regress/sql/index_including.sql b/src/test/regress/sql/index_including.sql
index 43bb6ea..db3c1bc 100644
--- a/src/test/regress/sql/index_including.sql
+++ b/src/test/regress/sql/index_including.sql
@@ -236,3 +236,12 @@ SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1;
RESET enable_seqscan;
DROP TABLE nametbl;
+
+/*
+ * 11. Expressions are not supported in included columns. Verify that we
+ * report a "feature not supported" error.
+ */
+CREATE TABLE tbl (c1 int, c2 int, c3 int);
+CREATE INDEX ON tbl (c1) INCLUDE ((c2 + c3));
+CREATE INDEX ON tbl (c1) INCLUDE ((c2));
+DROP TABLE tbl;
--
2.47.3
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-16 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maaz Syed Adeeb <maaz.adeeb@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2026-07-16 16:04 UTC (permalink / raw)
To: Maaz Syed Adeeb <maaz.adeeb@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; matthew.ripley28@gmail.com
Maaz Syed Adeeb <maaz.adeeb@gmail.com> writes:
> Attaching a patch with two regress tests. Let me know if I need to
> change anything.
Thanks. index_including.sql embodies all sorts of anti-patterns
for testing: use of very generic object names that could conflict
with concurrent test scripts, constant dropping and re-creation
of objects ensuring that the overhead per useful test is as high
as possible, etc. But it's not the job of this patch to improve
that, so I guess the fact that you faithfully copied the existing
style is fine. I used your test as-is and pushed it. Thanks
again for the report!
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-17 16:32 Maaz Syed Adeeb <maaz.adeeb@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Maaz Syed Adeeb @ 2026-07-17 16:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org; matthew.ripley28@gmail.com
> Thanks. index_including.sql embodies all sorts of anti-patterns
> for testing: use of very generic object names that could conflict
> with concurrent test scripts, constant dropping and re-creation
> of objects ensuring that the overhead per useful test is as high
> as possible, etc.
Thanks for pushing it. This one seems like another nice opportunity to
clean up testing anti-patterns. Apart from the two mentioned, are there any
other anti patterns here? And any previous attempts/discussions to make it
better? I'm happy to start a new thread to collect the things that can be
improved, not just for this test but for any others as well (if any), and
work incrementally on making the tests better. Let me know.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
@ 2026-07-17 17:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maaz Syed Adeeb <maaz.adeeb@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2026-07-17 17:42 UTC (permalink / raw)
To: Maaz Syed Adeeb <maaz.adeeb@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; matthew.ripley28@gmail.com
Maaz Syed Adeeb <maaz.adeeb@gmail.com> writes:
>> Thanks. index_including.sql embodies all sorts of anti-patterns
>> for testing: use of very generic object names that could conflict
>> with concurrent test scripts, constant dropping and re-creation
>> of objects ensuring that the overhead per useful test is as high
>> as possible, etc.
> Thanks for pushing it. This one seems like another nice opportunity to
> clean up testing anti-patterns. Apart from the two mentioned, are there any
> other anti patterns here?
The other thing that was irking me was that it numbers all the test
cases. That doesn't add any value, and what it does do is push
authors of new test cases very hard towards "add at the end", whether
or not that's the most sensible place for them in the overall
organization of the test script.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-07-17 17:42 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-15 13:05 CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master Maaz Syed Adeeb <maaz.adeeb@gmail.com>
2026-07-15 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-07-15 18:57 ` Maaz Syed Adeeb <maaz.adeeb@gmail.com>
2026-07-15 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-07-16 05:20 ` Maaz Syed Adeeb <maaz.adeeb@gmail.com>
2026-07-16 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-07-17 16:32 ` Maaz Syed Adeeb <maaz.adeeb@gmail.com>
2026-07-17 17:42 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox