UPSERT on view does not find constraint by name

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:

test=# CREATE TEMP TABLE foo (id int primary key);
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