Horton

Thread

Commits

  1. Correctly check updatability of columns targeted by INSERT...DEFAULT.

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

    The Post Office <noreply@postgresql.org> — 2024-07-20T09:00:01Z

    The following bug has been logged on the website:

Bug reference:      18546
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.com
PostgreSQL version: 17beta2
Operating system:   Ubuntu 22.04
Description:        

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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-07-20T16:26:29Z

    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

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

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2024-07-22T08:41:11Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-07-22T14:31:21Z

    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