Yet further fixes for multi-row VALUES lists for updatable views

Enterprise / PostgreSQL - Tom Lane [] - 11 October 2022 22:24 UTC

DEFAULT markers appearing in an INSERT on an updatable view could be mis-processed if they were in a multi-row VALUES clause. This would lead to strange errors such as "cache lookup failed for type NNNN", or in older branches even to crashes.

The cause is that commit 41531e42d tried to re-use rewriteValuesRTE() to remove any SetToDefault nodes (that hadn't previously been replaced by the view's own default values) appearing in "product" queries, that is DO ALSO queries. That's fundamentally wrong because the DO ALSO queries might not even be INSERTs; and even if they are, their targetlists don't necessarily match the view's column list, so that almost all the logic in rewriteValuesRTE() is inapplicable.

What we want is a narrow focus on replacing any such nodes with NULL constants. (That is, in this context we are interpreting the defaults as being strictly those of the view itself; and we already replaced any that aren't NULL.) We could add still more !force_nulls tests to further lobotomize rewriteValuesRTE(); but it seems cleaner to split out this case to a new function, restoring rewriteValuesRTE() to the charter it had before.

Per bug #17633 from jiye_sw. Patch by me, but thanks to Richard Guo and Japin Li for initial investigation. Back-patch to all supported branches, as the previous fix was.


b8f2687fdc Yet further fixes for multi-row VALUES lists for updatable views.
src/backend/rewrite/rewriteHandler.c | 87 ++++++++++++++++++---------
src/test/regress/expected/updatable_views.out | 22 +++++++
src/test/regress/sql/updatable_views.sql | 17 ++++++
3 files changed, 98 insertions(+), 28 deletions(-)


  • Share