Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

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