From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Avoid rewriting data-modifying CTEs more than once.
Date: Sat, 29 Nov 2025 12:36:31 +0000
Message-ID: <E1vPKBi-001yFr-2P@gemulon.postgresql.org> (raw)
Avoid rewriting data-modifying CTEs more than once.
Formerly, when updating an auto-updatable view, or a relation with
rules, if the original query had any data-modifying CTEs, the rewriter
would rewrite those CTEs multiple times as RewriteQuery() recursed
into the product queries. In most cases that was harmless, because
RewriteQuery() is mostly idempotent. However, if the CTE involved
updating an always-generated column, it would trigger an error because
any subsequent rewrite would appear to be attempting to assign a
non-default value to the always-generated column.
This could perhaps be fixed by attempting to make RewriteQuery() fully
idempotent, but that looks quite tricky to achieve, and would probably
be quite fragile, given that more generated-column-type features might
be added in the future.
Instead, fix by arranging for RewriteQuery() to rewrite each CTE
exactly once (by tracking the number of CTEs already rewritten as it
recurses). This has the advantage of being simpler and more efficient,
but it does make RewriteQuery() dependent on the order in which
rewriteRuleAction() joins the CTE lists from the original query and
the rule action, so care must be taken if that is ever changed.
Reported-by: Bernice Southey <bernice.southey@gmail.com>
Author: Bernice Southey <bernice.southey@gmail.com>
Author: Dean Rasheed <dean.a.rasheed@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://postgr.es/m/CAEDh4nyD6MSH9bROhsOsuTqGAv_QceU_GDvN9WcHLtZTCYM1kA@mail.gmail.com
Backpatch-through: 14
Branch
------
REL_17_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/c090965036631775942f5e4ff3c836fc6003c819
Modified Files
--------------
src/backend/rewrite/rewriteHandler.c | 31 ++++++++++++++++++++++-----
src/test/regress/expected/with.out | 41 ++++++++++++++++++++++++++++++++++++
src/test/regress/sql/with.sql | 23 ++++++++++++++++++++
3 files changed, 90 insertions(+), 5 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: dean.a.rasheed@gmail.com, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Avoid rewriting data-modifying CTEs more than once.
In-Reply-To: <E1vPKBi-001yFr-2P@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox