From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Peter Geoghegan <pg(at)heroku(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Craig Ringer <craig(at)2ndquadrant(dot)com> |
Subject: | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} |
Date: | 2014-09-25 14:35:23 |
Message-ID: | CA+TgmoZZchQiSu7dURZPhB+WnKqqiW1wzCKYxrMSbTJeZcHsww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2014-09-25 14:40:25 | Re: Scaling shared buffer eviction |
Previous Message | Merlin Moncure | 2014-09-25 14:34:57 | Re: Scaling shared buffer eviction |