On Thu, Sep 25, 2014 at 10:12 AM, Simon Riggs <si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers