Horton

Thread

Commits

  1. Fix infer_arbiter_indexes() to not assume resultRelation is 1.

  1. BUG #18502: Upsert on view returns 42P10 error when condition is an expression

    The Post Office <noreply@postgresql.org> — 2024-06-11T09:01:25Z

    The following bug has been logged on the website:

Bug reference:      18502
Logged by:          Michael Wang
Email address:      michael.wanghai.a@outlook.com
PostgreSQL version: 12.5
Operating system:   Official docker Image run in MacOS 14.3
Description:        

I have a table, index and view like following:
```
CREATE TABLE my_table
(
    id   uuid primary key,
    data jsonb not null
);
CREATE UNIQUE INDEX ON my_table ((data ->> 'key'));
CREATE VIEW my_view AS SELECT * FROM my_table;
```
The upsert on view returns 42P10 error when I execute the following SQL
```
INSERT INTO my_view (id, data)
VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
        '{
          "key": "value"
        }'::jsonb)
ON CONFLICT ((data ->> 'key'))
    DO NOTHING;
```
I tested cast expression and fails as well
```
CREATE UNIQUE INDEX ON my_table ((id::text));
INSERT INTO my_view (id, data)
VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
        '{
          "key": "value"
        }'::jsonb)
ON CONFLICT ((id::text))
    DO NOTHING;
```
I also tested `DO UPDATE...`, the same error is returned.
I also tested with the latest official docker image witch is PG 16, same
error returns.

  2. Re: BUG #18502: Upsert on view returns 42P10 error when condition is an expression

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-06-11T20:54:31Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> I have a table, index and view like following:
> ```
> CREATE TABLE my_table
> (
>     id   uuid primary key,
>     data jsonb not null
> );
> CREATE UNIQUE INDEX ON my_table ((data ->> 'key'));
> CREATE VIEW my_view AS SELECT * FROM my_table;
> ```
> The upsert on view returns 42P10 error when I execute the following SQL
> ```
> INSERT INTO my_view (id, data)
> VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
>         '{
>           "key": "value"
>         }'::jsonb)
> ON CONFLICT ((data ->> 'key'))
>     DO NOTHING;
> ```

For the archives' sake: the error being complained of is

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Thanks for the report.  The fault lies with infer_arbiter_indexes(),
which assumes that it can match the output of
RelationGetIndexExpressions or RelationGetIndexPredicate
directly to the query without adjusting varnos.  That works
most of the time, because the INSERT target typically has
varno 1, matching the way these trees are stored in the catalogs.
But not so much when we've flattened an updatable view;
so the match fails even when it should succeed.

The attached seems to be enough to fix it.

			regards, tom lane

  3. 回复: BUG #18502: Upsert on view returns 42P10 error when condition is an expression

    王 海 <michael.wanghai.a@outlook.com> — 2024-06-12T02:11:01Z

    Thanks for the analysis. May I ask how long will it cost normally for such bug fix being released since I'm new to the community? And will it be back port to PG 12?

Hai Wang
________________________________
发件人: Tom Lane <tgl@sss.pgh.pa.us>
发送时间: 2024年6月12日 4:54
收件人: michael.wanghai.a@outlook.com <michael.wanghai.a@outlook.com>
抄送: Peter Geoghegan <pg@bowt.ie>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
主题: Re: BUG #18502: Upsert on view returns 42P10 error when condition is an expression

PG Bug reporting form <noreply@postgresql.org> writes:
> I have a table, index and view like following:
> ```
> CREATE TABLE my_table
> (
>     id   uuid primary key,
>     data jsonb not null
> );
> CREATE UNIQUE INDEX ON my_table ((data ->> 'key'));
> CREATE VIEW my_view AS SELECT * FROM my_table;
> ```
> The upsert on view returns 42P10 error when I execute the following SQL
> ```
> INSERT INTO my_view (id, data)
> VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
>         '{
>           "key": "value"
>         }'::jsonb)
> ON CONFLICT ((data ->> 'key'))
>     DO NOTHING;
> ```

For the archives' sake: the error being complained of is

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Thanks for the report.  The fault lies with infer_arbiter_indexes(),
which assumes that it can match the output of
RelationGetIndexExpressions or RelationGetIndexPredicate
directly to the query without adjusting varnos.  That works
most of the time, because the INSERT target typically has
varno 1, matching the way these trees are stored in the catalogs.
But not so much when we've flattened an updatable view;
so the match fails even when it should succeed.

The attached seems to be enough to fix it.

                        regards, tom lane

  4. Re: BUG #18502: Upsert on view returns 42P10 error when condition is an expression

    David G. Johnston <david.g.johnston@gmail.com> — 2024-06-12T02:18:27Z

    On Tue, Jun 11, 2024 at 7:11 PM 王 海 <michael.wanghai.a@outlook.com> wrote:

> Thanks for the analysis. May I ask how long will it cost normally for such
> bug fix being released since I'm new to the community? And will it be back
> port to PG 12?
>
>
August according to the published roadmap:

https://www.postgresql.org/developer/roadmap/

And yes this one was back-patched to all supported versions which includes
v12 - though its time is up this year once v17 is released.

https://www.postgresql.org/support/versioning/

David J.