On Tue, May 5, 2015 at 8:40 AM, Andres Freund <and...@anarazel.de> wrote: > One additional thing I'm wondering about is the following: Right now > INSERT ... ON CONFLICT NOTHING does not acquire a row level lock on the > 'target' tuple. Are we really ok with that? Because in this form ON > CONFLICT NOTHING really doesn't guarantee much, the conflicting tuple > could just be deleted directly after the check. ISTM we should just > acquire the lock in the same way ExecOnConflictUpdate does. In the > majority of the cases that'll be what users actually expect > behaviourally.
Locking the row is not "nothing", though. If you want to lock the row, use an UPSERT with a tautologically false WHERE clause (like "WHERE false"). This is how other similar "ignore" features work in other systems, including MySQL, SQLite, and Oracle (which surprisingly has a hint that does this - surprising only because hints don't usually change the meaning of statements). It could make a big difference with a large bulk loading session, because just locking the rows will generate WAL and dirty pages. ETL is really the use-case for DO NOTHING. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
