pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Avoid rewriting data-modifying CTEs more than once. 6+ messages / 1 participants [nested] [flat]
* pgsql: Avoid rewriting data-modifying CTEs more than once. @ 2025-11-29 12:36 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 6+ messages in thread From: Dean Rasheed @ 2025-11-29 12:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 ------ master Details ------- https://git.postgresql.org/pg/commitdiff/3881561d7715647dbb4a5bc27f116504903daf1b 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid rewriting data-modifying CTEs more than once. @ 2025-11-29 12:36 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 6+ messages in thread From: Dean Rasheed @ 2025-11-29 12:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b880d9a025bd58d24e8bf03e64839794e7a982d9 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid rewriting data-modifying CTEs more than once. @ 2025-11-29 12:36 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 6+ messages in thread From: Dean Rasheed @ 2025-11-29 12:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid rewriting data-modifying CTEs more than once. @ 2025-11-29 12:36 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 6+ messages in thread From: Dean Rasheed @ 2025-11-29 12:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/4d288e33b957ce5023dd1ec24b53f02c8a9e8ba0 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid rewriting data-modifying CTEs more than once. @ 2025-11-29 12:36 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 6+ messages in thread From: Dean Rasheed @ 2025-11-29 12:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/134a8ee2240557e8165cffe70d19e48937cece03 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid rewriting data-modifying CTEs more than once. @ 2025-11-29 12:36 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 6+ messages in thread From: Dean Rasheed @ 2025-11-29 12:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2d5b97b8c6542791604e5422ca5d90b011af70a1 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-11-29 12:36 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-11-29 12:36 pgsql: Avoid rewriting data-modifying CTEs more than once. Dean Rasheed <dean.a.rasheed@gmail.com> 2025-11-29 12:36 pgsql: Avoid rewriting data-modifying CTEs more than once. Dean Rasheed <dean.a.rasheed@gmail.com> 2025-11-29 12:36 pgsql: Avoid rewriting data-modifying CTEs more than once. Dean Rasheed <dean.a.rasheed@gmail.com> 2025-11-29 12:36 pgsql: Avoid rewriting data-modifying CTEs more than once. Dean Rasheed <dean.a.rasheed@gmail.com> 2025-11-29 12:36 pgsql: Avoid rewriting data-modifying CTEs more than once. Dean Rasheed <dean.a.rasheed@gmail.com> 2025-11-29 12:36 pgsql: Avoid rewriting data-modifying CTEs more than once. Dean Rasheed <dean.a.rasheed@gmail.com>
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