On Wed, 2014-10-08 at 01:10 -0700, Peter Geoghegan wrote:
> On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen
> <anssi.kaariai...@thl.fi> wrote:
> > The MySQL documentation says that "you should try to avoid using an ON
> > DUPLICATE KEY UPDATE clause on tables with multiple unique indexes"[1].
> > The proposed feature's documentation has the same suggestion[2]. Still,
> > the feature defaults to this behavior. Why is the default something the
> > documentation says you shouldn't do?

> As we all know, naming a unique index in DML is ugly, and has poor
> support in ORMs. It seems likely that we're better off making it
> optional - it hasn't been much of a problem with the existing subxact
> looping pattern.

The subxact approach is a bit different than the proposed UPSERT
command. It loops:

       INSERT INTO author VALUE('Jack', 't...@example.com', 34)
   except UniqueConstraintViolation:
       UPDATE author SET ... WHERE name = 'Jack'

while the UPSERT command does something like:

       INSERT INTO author VALUE('Jack', 't...@example.com', 34)
   except UniqueConstaintViolation:
       UPDATE author SET ... WHERE name = 'Jack' OR email = 't...@example.com' 

 - Anssi

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

Reply via email to