It looks like we are close to reaching consensus on the syntax. Phew! Thanks for maintaining the wiki pages and the documentation. All of the below is based on those, I haven't looked at the patch itself yet.

The one thing that I still feel uneasy about is the Unique Index Inference thing. Per the syntax example from the wiki page, the UPSERT statement looks like this:

INSERT INTO upsert(key, val) VALUES(1, 'insert')
   ON CONFLICT (key) IGNORE;

With ON CONFLICT IGNORE, the list of key columns can also be left out:

INSERT INTO upsert(key, val) VALUES(1, 'insert')
   ON CONFLICT IGNORE;

The documentation says that:

Omitting the specification indicates a total indifference to where
any would-be uniqueness violation could occur, which isn't always
appropriate; at times, it may be desirable for ON CONFLICT IGNORE to
not suppress a duplicate violation within an index where that isn't
explicitly anticipated. Note that ON CONFLICT UPDATE assignment may
result in a uniqueness violation, just as with a conventional
UPDATE.

Some questions:

1. Does that mean that if you leave out the key columns, the insertion is IGNOREd if it violates *any* unique key constraint?

2. If you do specify the key columns, then the IGNORE path is taken only if the insertion violates a unique key constraint on those particular columns. Otherwise an error is thrown. Right? Now, let's imagine a table like this:

CREATE TABLE persons (
  username text unique,
  real_name text unique,
  data text
);

Is there any way to specify both of those constraints, so that the insertion is IGNOREd if it violates either one of them? If you try to do:

INSERT INTO persons(username, real_name, data)
VALUES('foobar', 'foo bar')
ON CONFLICT (username, real_name) IGNORE;

It will fail because there is no unique index on (username, real_name). In this particular case, you could leave out the specification, but if there was a third constraint that you're not expecting to conflict with, you would want violations of that constraint to still throw an error. And you can't leave out the specification with ON CONFLICT UPDATE anyway.

3. Why is the specification required with ON CONFLICT UPDATE, but not with ON CONFLICT IGNORE?

4. What happens if there are multiple unique indexes with identical columns, and you give those columns in the inference specification? Doesn't matter which index you use, I guess, if they're all identical, but see next question.

5. What if there are multiple unique indexes with the same columns, but different operator classes?

6. Why are partial unique indexes not supported as arbitrators?

- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to