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

On Thu, Sep 25, 2014 at 10:12 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> IMHO it is impossible to know if any of the other code is correct
> until we have a clear and stable vision of what the command is
> supposed to perform.

+1.

> The inner workings are less important than what the feature does.

+1.

> FWIW, the row available at the end of all BEFORE triggers is clearly
> the object we should be manipulating, not the original VALUES()
> clause. Otherwise this type of INSERT would behave differently from
> normal INSERTs. Which would likely violate RLS, if nothing else.

+1.

> Surely if there are multiple unique indexes then the result row must
> be validated against all unique indexes before it is allowed at all?
>
> The only problem I see is if the newly inserted row matches one row on
> one unique value and a different row on a different unique index.
> Turning the INSERT into an UPDATE will still fail on one or other, no
> matter which index you pick. If there is one row for ALL unique
> indexes then it is irrelevant which index you pick. So either way, I
> cannot see a reason to specify an index.

Failure could be the right thing in some cases. For example, imagine
that a user has a table containing names, email addresses, and (with
apologies for the American-ism, but I don't know what would be
comparable elsewhere) social security numbers. The user has unique
indexes on both email addresses and SSNs. If a new record arrives for
the same email address, they want to replace the existing record; but
a new record arrives with the same SSN, they want the transaction to
fail. Otherwise, a newly-arrived record might overwrite the email
address of an existing record, which they never want to do, because
they view email address as the primary key.

I think this kind of scenario will actually be pretty common.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company