Re: INSERT...ON DUPLICATE KEY IGNORE

On 08/31/2013 06:40 AM, Josh Berkus wrote:
>> > 3) RETURNING is expanded - "RETURNING REJECTS *" is now possible where
>> > that makes sense.
> Oh, nifty! OK, now I can *really* use this feature.

Absolutely; especially combined with COPY to a staging TEMPORARY or
UNLOGGED table.

It'll be yet another way for people to get upsert wrong, of course.
They'll use a wCTE with RETURNING REJECTS to do an UPDATE of the rejects
w/o locking the table against writes first. Documenting this pitfall
should be enough, though.

Speaking of upsert, I'm starting to think that to solve the upsert
problem without forcing full-table locking on people we need a lock type
that conflicts only with DELETE/INSERT and a way to prevent UPDATEs from
changing the key. Kind of like the table level inverse of FOR KEY UPDATE
- a way to say "you can change rows, just cannot add or remove from the
set of keys".

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services