BUG #19632: RULE rewriting crashes with XX000 when RETURNING old/new references a system column
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253484psql -h localhost -U postgresBuilt from patchset v6 (message #6), August 20, 2026 at 09:10 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253484_6 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253484_6 && git checkout t253484_6Patchset v6 (message #6) is on t253484_6
The following bug has been logged on the website:
Bug reference: 19632
Logged by: Zheng Hacker
Email address: hackerzheng666@gmail.com
PostgreSQL version: 19beta3
Operating system: Linux x86_64
Description:
PostgreSQL version: 20devel (commit bdbf662, 2026-08-19)
OS: Linux x86_64
When a DML query with RETURNING old.<system_column> or
RETURNING new.<system_column> (PG 20 new syntax) is rewritten
through a RULE, the query rewriter cannot find replacement
targetlist entries for system columns, hitting elog(ERROR) in
rewriteManip.c.
Reproducer:
CREATE TABLE t (a int);
INSERT INTO t VALUES (1);
CREATE RULE t_del AS ON DELETE TO t
DO INSTEAD UPDATE t SET a = -1 WHERE a = OLD.a RETURNING *;
-- All of these crash with XX000:
DELETE FROM t WHERE a = 1 RETURNING old.tableoid;
-- ERROR: XX000: could not find replacement targetlist entry for attno -6
-- LOCATION: ReplaceVarFromTargetList, rewriteManip.c:1884
DELETE FROM t WHERE a = 1 RETURNING old.ctid; -- attno -1
DELETE FROM t WHERE a = 1 RETURNING new.tableoid; -- attno -6
-- Without the RULE, the same RETURNING clause works correctly:
DROP RULE t_del ON t;
DELETE FROM t WHERE a = 1 RETURNING old.tableoid; -- works fine
Root cause: src/backend/rewrite/rewriteManip.c, function
ReplaceVarFromTargetList (line 1884). When the rewriter processes
the RULE's action to replace Vars, it iterates over the action's
target list looking for an entry with matching resno. System
columns have negative attribute numbers (e.g. tableoid = -6), but
the RULE's RETURNING target list only contains user-defined columns
(with positive resnos), so no match is found.
The PG 20 old/new RETURNING syntax (var->varreturningtype !=
VAR_RETURNING_DEFAULT) is handled AFTER the targetlist entry lookup
succeeds (lines 1894-1910), so the code never reaches that logic
for system columns.
Affects all system columns (tableoid, ctid, xmin, cmin, xmax)
through any RULE that uses DO INSTEAD.
Found by automated SQL fuzzing.
Credit: Zheng Wang, Yanjie Zhao, Yiyang Liu
Hi,
On Wed, 19 Aug 2026 at 14:54, PG Bug reporting form <noreply@postgresql.org>
wrote:
The following bug has been logged on the website:
Bug reference: 19632
Logged by: Zheng Hacker
Email address: hackerzheng666@gmail.com
PostgreSQL version: 19beta3
Operating system: Linux x86_64
Description:PostgreSQL version: 20devel (commit bdbf662, 2026-08-19)
OS: Linux x86_64When a DML query with RETURNING old.<system_column> or
RETURNING new.<system_column> (PG 20 new syntax) is rewritten
through a RULE, the query rewriter cannot find replacement
targetlist entries for system columns, hitting elog(ERROR) in
rewriteManip.c.Reproducer:
CREATE TABLE t (a int);
INSERT INTO t VALUES (1);
CREATE RULE t_del AS ON DELETE TO t
DO INSTEAD UPDATE t SET a = -1 WHERE a = OLD.a RETURNING *;-- All of these crash with XX000:
DELETE FROM t WHERE a = 1 RETURNING old.tableoid;
-- ERROR: XX000: could not find replacement targetlist entry for attno -6
-- LOCATION: ReplaceVarFromTargetList, rewriteManip.c:1884DELETE FROM t WHERE a = 1 RETURNING old.ctid; -- attno -1
DELETE FROM t WHERE a = 1 RETURNING new.tableoid; -- attno -6-- Without the RULE, the same RETURNING clause works correctly:
DROP RULE t_del ON t;
DELETE FROM t WHERE a = 1 RETURNING old.tableoid; -- works fineRoot cause: src/backend/rewrite/rewriteManip.c, function
ReplaceVarFromTargetList (line 1884). When the rewriter processes
the RULE's action to replace Vars, it iterates over the action's
target list looking for an entry with matching resno. System
columns have negative attribute numbers (e.g. tableoid = -6), but
the RULE's RETURNING target list only contains user-defined columns
(with positive resnos), so no match is found.The PG 20 old/new RETURNING syntax (var->varreturningtype !=
VAR_RETURNING_DEFAULT) is handled AFTER the targetlist entry lookup
succeeds (lines 1894-1910), so the code never reaches that logic
for system columns.Affects all system columns (tableoid, ctid, xmin, cmin, xmax)
through any RULE that uses DO INSTEAD.Found by automated SQL fuzzing.
Credit: Zheng Wang, Yanjie Zhao, Yiyang Liu
Thanks for the report.
It seems all 19629 to 19632 bug is the same line of issue, and there
might be more such cases where ERRCODE is not added
across the tree. We can accumulate all such cases and do a single
commit for them.
Regards,
Ayush
Hello
This also seems reproducible on PG 18.
I think there could be some corner-cases where supporting this would
be useful, for example SQLAlchemy supports locking via xmin[1]https://docs.sqlalchemy.org/en/21/orm/versioning.html#server-side-version-counters, and a
soft delete rule (ON DELETE DO INSTEAD UPDATE ... SET deleted=true)
could break that.
We could support something like this with a new syntax perhaps?
CREATE RULE ... DO INSTEAD ... RETURNING * WITH SYSTEM COLUMNS (xmin
AS t.xmin ....);
However, that would be a new feature. The safe and easy choice (at
least for 18/19) seems to be reporting a proper error message.
The attached patch improves the issue by adding that error message.
[1]: https://docs.sqlalchemy.org/en/21/orm/versioning.html#server-side-version-counters
On Wed, Aug 19, 2026 at 11:56:19AM +0100, Zsolt Parragi wrote:
Hello
This also seems reproducible on PG 18.
I think there could be some corner-cases where supporting this would
be useful, for example SQLAlchemy supports locking via xmin[1], and a
soft delete rule (ON DELETE DO INSTEAD UPDATE ... SET deleted=true)
could break that.We could support something like this with a new syntax perhaps?
Target list replacements have never coped with negative attnums in the
rewrite paths, as far as I can see.
CREATE RULE ... DO INSTEAD ... RETURNING * WITH SYSTEM COLUMNS (xmin
AS t.xmin ....);
FWIW, I tend to see rules as relics of the past. Triggers are for one
more useful in modern systems, so I cannot really get excited with an
extension of the grammar to support an edge case that has, as far as I
know, never worked since we support RETURNING.
However, that would be a new feature. The safe and easy choice (at
least for 18/19) seems to be reporting a proper error message.
An error message sounds like a protection good enough for me. Whether
someone is motivated to support such cases is a different question,
but IMO we may be closer with removing support for rules than trying
to extend it.
--
Michael
FWIW, I tend to see rules as relics of the past. Triggers are for one
more useful in modern systems, so I cannot really get excited with an
extension of the grammar to support an edge case that has, as far as I
know, never worked since we support RETURNING.
Right, I thought you can't implement soft delete with triggers but of
course you can. Except that RETURNING doesn't work at all in that
case, unless I make it a delete+insert or also introduce a helper
view, but even with the view I can only get out the old xmin, not the
new.
Anyway, that's a different issue.
On Wed, 19 Aug 2026, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
It seems all 19629 to 19632 bug is the same line of issue, and there
might be more such cases where ERRCODE is not added
across the tree. We can accumulate all such cases and do a single
commit for them.
I agree that it would be better to handle all this in this commit, but
the patch you shared in [1]/messages/by-id/CAJTYsWWpwHsvzKp0JPvGbWttXrOafMCtmGBBQs6bhCKn88fgug@mail.gmail.com seems to be missing this change (the
commit message also only references ugs up to 31). And I think we
should also fix the error message for the FOR PORTION[2]/messages/by-id/CAN4CZFPsGAs3x5oBi5YiUyFdJgJrxfp=Hi3xscq_Y-HLMCs1kQ@mail.gmail.com issue.
[1]: /messages/by-id/CAJTYsWWpwHsvzKp0JPvGbWttXrOafMCtmGBBQs6bhCKn88fgug@mail.gmail.com
[2]: /messages/by-id/CAN4CZFPsGAs3x5oBi5YiUyFdJgJrxfp=Hi3xscq_Y-HLMCs1kQ@mail.gmail.com
Hi,
On Thu, 20 Aug 2026 at 13:59, Zsolt Parragi <zsolt.parragi@percona.com>
wrote:
On Wed, 19 Aug 2026, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
It seems all 19629 to 19632 bug is the same line of issue, and there
might be more such cases where ERRCODE is not added
across the tree. We can accumulate all such cases and do a single
commit for them.I agree that it would be better to handle all this in this commit, but
the patch you shared in [1] seems to be missing this change (the
commit message also only references ugs up to 31). And I think we
should also fix the error message for the FOR PORTION[2] issue.
Right, I initially thought of that, but I had prepared a different patch
for this, and I was going to post, but it was quite similar to the one
you already did. However, yes we can handle it all in the same
commit.
Here's a combined patch attached with all the 4 bugs addressed.
[Please help review if I missed something, also including Fujii-san
since he was working on one of them]
Regards,
Ayush
On Thu, Aug 20, 2026 at 02:28:17PM +0530, Ayush Tiwari wrote:
Right, I initially thought of that, but I had prepared a different patch
for this, and I was going to post, but it was quite similar to the one
you already did. However, yes we can handle it all in the same
commit.
Handling them separately feels fine here. That's just tackling one
problem at a time, looking at each problem separately..
Here's a combined patch attached with all the 4 bugs addressed.
[Please help review if I missed something, also including Fujii-san
since he was working on one of them]
Please do not digress the subject of this thread. Cross-posting the
same message across multiple places just makes the whole confusing..
--
Michael
On Thu, Aug 20, 2026 at 09:29:36AM +0100, Zsolt Parragi wrote:
Right, I thought you can't implement soft delete with triggers but of
course you can. Except that RETURNING doesn't work at all in that
case, unless I make it a delete+insert or also introduce a helper
view, but even with the view I can only get out the old xmin, not the
new.
For now I have applied your patch on HEAD as of d39762b8c79a,
qualifying as a life improvement thing with a better message. I have
never really seen somebody complain about that limitation, as well..
--
Michael
On 2026-Aug-19, PG Bug reporting form wrote:
Reproducer:
CREATE TABLE t (a int);
INSERT INTO t VALUES (1);
CREATE RULE t_del AS ON DELETE TO t
DO INSTEAD UPDATE t SET a = -1 WHERE a = OLD.a RETURNING *;-- All of these crash with XX000:
DELETE FROM t WHERE a = 1 RETURNING old.tableoid;
-- ERROR: XX000: could not find replacement targetlist entry for attno -6
This is not a "crash". The server is still running, no process has been
restarted, so what you see here is an ordinary error with no further
interesting symptoms. This report is a bit alarmist.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/