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:
try:
INSERT INTO author VALUE('Jack', 't...@example.com', 34)
except UniqueConstraintViolation:
UPDATE author SET ... WHERE name = 'Jack'
while the UPSERT command does something like:
try:
INSERT INTO author VALUE('Jack', 't...@example.com', 34)
except UniqueConstaintViolation:
UPDATE author SET ... WHERE name = 'Jack' OR email = 't...@example.com'
LIMIT 1;
- Anssi
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
