pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Expand virtual generated columns in the planner 3+ messages / 2 participants [nested] [flat]
* pgsql: Expand virtual generated columns in the planner @ 2025-02-25 07:12 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Richard Guo @ 2025-02-25 07:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Expand virtual generated columns in the planner Commit 83ea6c540 added support for virtual generated columns that are computed on read. All Var nodes in the query that reference virtual generated columns must be replaced with the corresponding generation expressions. Currently, this replacement occurs in the rewriter. However, this approach has several issues. If a Var referencing a virtual generated column has any varnullingrels, those varnullingrels need to be propagated into the generation expression. Failing to do so can lead to "wrong varnullingrels" errors and improper outer-join removal. Additionally, if such a Var comes from the nullable side of an outer join, we may need to wrap the generation expression in a PlaceHolderVar to ensure that it is evaluated at the right place and hence is forced to null when the outer join should do so. In certain cases, such as when the query uses grouping sets, we also need a PlaceHolderVar for anything that is not a simple Var to isolate subexpressions. Failure to do so can result in incorrect results. To fix these issues, this patch expands the virtual generated columns in the planner rather than in the rewriter, and leverages the pullup_replace_vars architecture to avoid code duplication. The generation expressions will be correctly marked with nullingrel bits and wrapped in PlaceHolderVars when needed by the pullup_replace_vars callback function. This requires handling the OLD/NEW RETURNING list Vars in pullup_replace_vars_callback, as it may now deal with Vars referencing the result relation instead of a subquery. The "wrong varnullingrels" error was reported by Alexander Lakhin. The incorrect result issue and the improper outer-join removal issue were reported by Richard Guo. Author: Richard Guo <guofenglinux@gmail.com> Author: Dean Rasheed <dean.a.rasheed@gmail.com> Reviewed-by: Jian He <jian.universality@gmail.com> Discussion: https://postgr.es/m/75eb1a6f-d59f-42e6-8a78-124ee808cda7@gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/1e4351af329f2949c679a215f63c51d663ecd715 Modified Files -------------- src/backend/optimizer/plan/planner.c | 8 + src/backend/optimizer/prep/prepjointree.c | 196 ++++++++++++++++++++++++ src/backend/rewrite/rewriteHandler.c | 91 +++++------ src/backend/rewrite/rewriteManip.c | 2 +- src/include/nodes/primnodes.h | 2 +- src/include/optimizer/prep.h | 1 + src/include/rewrite/rewriteHandler.h | 1 + src/include/rewrite/rewriteManip.h | 3 + src/test/regress/expected/generated_virtual.out | 130 ++++++++++++++++ src/test/regress/sql/generated_virtual.sql | 57 +++++++ 10 files changed, 445 insertions(+), 46 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Expand virtual generated columns in constraint expressions @ 2025-09-15 14:33 Peter Eisentraut <peter@eisentraut.org> 0 siblings, 0 replies; 3+ messages in thread From: Peter Eisentraut @ 2025-09-15 14:33 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Expand virtual generated columns in constraint expressions Virtual generated columns in constraint expressions need to be expanded because the optimizer matches these expressions to qual clauses. Failing to do so can cause us to miss opportunities for constraint exclusion. Author: Richard Guo <guofenglinux@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/204804c0-798f-4c72-bd1f-36116024fda3%40eisentraut.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/ce71993ae46930ddb1ff790807114384898b86a7 Modified Files -------------- src/backend/optimizer/util/plancat.c | 9 +++++++++ src/test/regress/expected/generated_virtual.out | 23 +++++++++++++++++++++++ src/test/regress/sql/generated_virtual.sql | 15 +++++++++++++++ 3 files changed, 47 insertions(+) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Expand virtual generated columns in constraint expressions @ 2025-09-15 14:33 Peter Eisentraut <peter@eisentraut.org> 0 siblings, 0 replies; 3+ messages in thread From: Peter Eisentraut @ 2025-09-15 14:33 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Expand virtual generated columns in constraint expressions Virtual generated columns in constraint expressions need to be expanded because the optimizer matches these expressions to qual clauses. Failing to do so can cause us to miss opportunities for constraint exclusion. Author: Richard Guo <guofenglinux@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/204804c0-798f-4c72-bd1f-36116024fda3%40eisentraut.org Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/e7a2bbdb522dd6fc7283550742b8b52605198783 Modified Files -------------- src/backend/optimizer/util/plancat.c | 9 +++++++++ src/test/regress/expected/generated_virtual.out | 23 +++++++++++++++++++++++ src/test/regress/sql/generated_virtual.sql | 15 +++++++++++++++ 3 files changed, 47 insertions(+) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-09-15 14:33 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-25 07:12 pgsql: Expand virtual generated columns in the planner Richard Guo <rguo@postgresql.org> 2025-09-15 14:33 pgsql: Expand virtual generated columns in constraint expressions Peter Eisentraut <peter@eisentraut.org> 2025-09-15 14:33 pgsql: Expand virtual generated columns in constraint expressions Peter Eisentraut <peter@eisentraut.org>
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