Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

On Tue, May 5, 2015 at 8:40 AM, Andres Freund <andres(at)anarazel(dot)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