Fix DEFAULT-handling in multi-row VALUES lists for updatable views

Enterprise / PostgreSQL - Dean Rasheed [gmail.com] - 20 February 2019 08:30 EST

INSERT ... VALUES for a single VALUES row is implemented differently from a multi-row VALUES list, which causes inconsistent behaviour in the way that DEFAULT items are handled. In particular, when inserting into an auto-updatable view on top of a table with a column default, a DEFAULT item in a single VALUES row gets correctly replaced with the table column's default, but for a multi-row VALUES list it is replaced with NULL.

Fix this by allowing rewriteValuesRTE() to leave DEFAULT items in the
VALUES list untouched if the target relation is an auto-updatable view and has no column default, deferring DEFAULT-expansion until the query against the base relation is rewritten. For all other types of target relation, including tables and trigger- and rule-updatable views, we must continue to replace DEFAULT items with NULL in the absence of a column default.

This is somewhat complicated by the fact that if an auto-updatable
view has DO ALSO rules attached, the VALUES lists for the product queries need to be handled differently from the original query, since the product queries need to act like rule-updatable views whereas the original query has auto-updatable view semantics.

Back-patch to all supported versions.

Reported by Roger Curley (bug #15623). Patch by Amit Langote and me.

Discussion: https://postgr.es/m/15623-5d67a46788ec8b7f@postgresql.org

41531e42d3 Fix DEFAULT-handling in multi-row VALUES lists for updatable views.
src/backend/rewrite/rewriteHandler.c | 139 +++++++++++++++++++++--
src/test/regress/expected/updatable_views.out | 153 ++++++++++++++++++++++++++
src/test/regress/sql/updatable_views.sql | 88 +++++++++++++++
3 files changed, 369 insertions(+), 11 deletions(-)

Upstream: git.postgresql.org


  • Share