Обсуждение: UPSERT on view does not find constraint by name
I'm not sure if this can be considered a bug or not, but it is perhaps unexpected. I found that when using a view that is simply select * from table, then doing INSERT ... ON CONFLICT ON CONSTRAINT constraint_name on that view, it does not find the constraint and errors out. But it does find the constraint if one lists the columns instead.
I did not find any mention of this specifically in the docs, or any discussion on this topic after a brief search, and I have already asked my stakeholder to change to using the column list as better practice anyway. But in any case, I wanted to know if this is a known issue or not.
Thanks!
Jeremy
Jeremy Finzel <finzelj@gmail.com> writes:
> I'm not sure if this can be considered a bug or not, but it is perhaps
> unexpected. I found that when using a view that is simply select * from
> table, then doing INSERT ... ON CONFLICT ON CONSTRAINT constraint_name on
> that view, it does not find the constraint and errors out. But it does
> find the constraint if one lists the columns instead.
I'm confused by this report. The view wouldn't have any constraints,
and experimenting shows that the parser won't let you name a
constraint of the underlying table here. So would you provide a
concrete example of what you're talking about?
regards, tom lane
On Fri, Oct 18, 2019 at 3:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeremy Finzel <finzelj@gmail.com> writes:
> I'm not sure if this can be considered a bug or not, but it is perhaps
> unexpected. I found that when using a view that is simply select * from
> table, then doing INSERT ... ON CONFLICT ON CONSTRAINT constraint_name on
> that view, it does not find the constraint and errors out. But it does
> find the constraint if one lists the columns instead.
I'm confused by this report. The view wouldn't have any constraints,
and experimenting shows that the parser won't let you name a
constraint of the underlying table here. So would you provide a
concrete example of what you're talking about?
regards, tom lane
Apologies for the lack of clarity. Here is a simple example of what I mean:
CREATE TABLE
test=# \d foo
Table "pg_temp_4.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
test=# CREATE VIEW bar AS SELECT * FROM foo;
NOTICE: view "bar" will be a temporary view
CREATE VIEW
test=# INSERT INTO foo (id)
test-# VALUES (1)
test-# ON CONFLICT ON CONSTRAINT foo_pkey
test-# DO NOTHING;
INSERT 0 1
test=# INSERT INTO foo (id)
VALUES (1)
ON CONFLICT ON CONSTRAINT foo_pkey
DO NOTHING;
INSERT 0 0
test=# INSERT INTO foo (id)
VALUES (1)
ON CONFLICT ON CONSTRAINT foo_pkey
DO NOTHING;
INSERT 0 0
test=# INSERT INTO bar (id)
VALUES (1)
ON CONFLICT ON CONSTRAINT foo_pkey
DO NOTHING;
ERROR: constraint "foo_pkey" for table "bar" does not exist
test=# INSERT INTO bar (id)
VALUES (1)
ON CONFLICT (id)
DO NOTHING;
INSERT 0 0
Of interest are the last 2 statements above. ON CONFLICT on the constraint name does not work, but it does work by field name. I'm not saying it *should* work both ways, but I'm more wondering if this is known/expected/desired behavior.
The point of interest for us is that we frequently preserve a table's "public API" by instead swapping out a table for a view as above, in order for instance to rebuild a table behind the scenes without breaking table usage. Above case is a rare example where that doesn't work, and which in any case I advise (as does the docs) that they do not use on conflict on constraint, but rather to list the field names instead.
Thanks,
Jeremy
Jeremy Finzel <finzelj@gmail.com> writes:
> test=# CREATE TEMP TABLE foo (id int primary key);
> CREATE TABLE
> test=# CREATE VIEW bar AS SELECT * FROM foo;
> NOTICE: view "bar" will be a temporary view
> CREATE VIEW
> ...
> test=# INSERT INTO bar (id)
> VALUES (1)
> ON CONFLICT ON CONSTRAINT foo_pkey
> DO NOTHING;
> ERROR: constraint "foo_pkey" for table "bar" does not exist
> test=# INSERT INTO bar (id)
> VALUES (1)
> ON CONFLICT (id)
> DO NOTHING;
> INSERT 0 0
> Of interest are the last 2 statements above. ON CONFLICT on the constraint
> name does not work, but it does work by field name. I'm not saying it
> *should* work both ways, but I'm more wondering if this is
> known/expected/desired behavior.
The first case looks perfectly normal to me: there is no "foo_pkey"
constraint associated with the "bar" view. It is interesting that
the second case drills down to find there's an underlying constraint,
but that seems like a bit of a hack :-(.
Poking at it a little more closely, it seems like the first case
involves a parse-time constraint lookup, while the second case
postpones the lookup to plan time, and so the second case works
because the view has already been expanded into a direct reference
to the underlying table. Maybe it wasn't good to do those cases
differently. I can't get too excited about it though.
regards, tom lane