CREATE INDEX with an expression in an INCLUDE column fails with XX000 "unrecognized node type" instead of 0A000 on master
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=181b6185c79e09e6ac94428189d9afac807244ac
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
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:
wip-fix-index-included-expressions.patchtext/x-diff; charset=us-ascii; name=wip-fix-index-included-expressions.patchDownload+20-0
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
Show quoted text
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
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
Attaching a patch with two regress tests. Let me know if I need to
change anything.
Regards,
Maaz Syed Adeeb
Show quoted text
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:
v1-0001-Apply-parse-transformation-to-expressions-in-INCL.patchapplication/octet-stream; name=v1-0001-Apply-parse-transformation-to-expressions-in-INCL.patchDownload+43-1
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
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.
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