BUG #18546: Attempt to insert default into a non-updatable column of a view fails with a dubious error

PG Bug reporting form <noreply@postgresql.org> writes:

The following query:
CREATE TABLE t (a int);
CREATE VIEW v AS SELECT a, a + 0 AS a0 FROM t;
INSERT INTO v values (default, default);
raises
ERROR: XX000: attribute number 2 not found in view targetlist
LOCATION: adjust_view_column_set, rewriteHandler.c:3045

Whilst
INSERT INTO v values (default, 1);
fails with clearer
ERROR: 0A000: cannot insert into column "a0" of view "v"
DETAIL: View columns that are not columns of their base relation are not
updatable.

Interesting. I thought this was a wrong-order-of-checks problem,
but it's more subtle than that. The "cannot insert into column "a0""
message is produced when view_cols_are_auto_updatable recognizes that
we can't assign to that particular column. But
view_cols_are_auto_updatable doesn't test the a0 column, because
it's told to check the columns that are targeted by the query
targetlist after rewriteTargetListIU ... and rewriteTargetListIU has
thrown away the DEFAULT markers, on the grounds that the column
defaults are null and we don't need to represent that explicitly.

The existing code/comments (dating AFAICS to Dean's cab5dc5da)
already point out that rewriteTargetListIU can add targetlist items,
but we missed the fact that it can delete them too. So it seems like
what we need to do is union the original set of target columns with
what's listed in the targetlist, as attached. I suppose we could
also rethink the decision to throw away null defaults, but that seems
much more invasive.

Thanks for the report!

regards, tom lane

On Sat, 20 Jul 2024 at 17:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The existing code/comments (dating AFAICS to Dean's cab5dc5da)
already point out that rewriteTargetListIU can add targetlist items,
but we missed the fact that it can delete them too. So it seems like
what we need to do is union the original set of target columns with
what's listed in the targetlist, as attached.

Ah yes, that makes sense and the fix looks good. Thanks for taking care of that.

I had always thought that rewriteTargetListIU() only ever added or
merged items, somehow overlooking the fact that it could also delete
them. In my defence, it's very easy to get that impression just by
reading the function's comments. I think it's worth updating those
comments to mention that. Something like the attached, perhaps.

Regards,
Dean

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

I had always thought that rewriteTargetListIU() only ever added or
merged items, somehow overlooking the fact that it could also delete
them. In my defence, it's very easy to get that impression just by
reading the function's comments. I think it's worth updating those
comments to mention that. Something like the attached, perhaps.

No objection here.

regards, tom lane