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

On Sun, Apr 26, 2015 at 6:02 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> * I privately pointed out to Heikki what I'd said publicly about 6
> weeks ago: that there is still a *very* small chance of exclusion
> constraints exhibiting "unprincipled deadlocks" (he missed it at the
> time). I think that this risk is likely to be acceptable, since it
> takes so much to see it happen (and ON CONFLICT UPDATE/nbtree is
> unaffected). But let's better characterize the risks, particularly in
> light of the changes to store speculative tokens in the c_ctid field
> on newly inserted (speculative) tuples. I think that that probably
> made the problem significantly less severe, and perhaps it's now
> entirely theoretical, but I want to make sure. I'm going to try and
> characterize the risks with the patch here today.

So, this can still happen, but is now happening less often than
before, I believe. On a 16 core server, with continual 128 client
jjanes_upsert exclusion constraint only runs, with fsync=off, I
started at this time:

2015-04-27 21:22:28 UTC [ 0 ]: LOG: database system was shut down at
2015-04-27 21:22:25 UTC
2015-04-27 21:22:28 UTC [ 0 ]: LOG: database system is ready to
accept connections
2015-04-27 22:47:20 UTC [ 0 ]: LOG: autovacuum launcher started
2015-04-27 22:47:21 UTC [ 0 ]: LOG: autovacuum launcher started

Finally, with ON CONFLICT UPDATE (which we don't intend to support
with exclusion constraints anyway), the torture testing finally
produces a deadlock several hours later (due to having "livelock
insurance" [1]):

2015-04-28 00:22:06 UTC [ 0 ]: LOG: autovacuum launcher started
2015-04-28 00:37:24 UTC [ 432432057 ]: ERROR: deadlock detected
2015-04-28 00:37:24 UTC [ 432432057 ]: DETAIL: Process 130628 waits
for ShareLock on transaction 432432127; blocked by process 130589.
Process 130589 waits for ShareLock on speculative token 13 of
transaction 432432057; blocked by process 130628.
Process 130628: insert into upsert_race_test (index, count)
values ('7566','-1') on conflict
update set count=TARGET.count + EXCLUDED.count
where TARGET.index = EXCLUDED.index
returning count
Process 130589: insert into upsert_race_test (index, count)
values ('7566','1') on conflict
update set count=TARGET.count + EXCLUDED.count
where TARGET.index = EXCLUDED.index
returning count
2015-04-28 00:37:24 UTC [ 432432057 ]: HINT: See server log for query details.
2015-04-28 00:37:24 UTC [ 432432057 ]: CONTEXT: while checking
exclusion constraint on tuple (3,36) in relation "upsert_race_test"
2015-04-28 00:37:24 UTC [ 432432057 ]: STATEMENT: insert into
upsert_race_test (index, count) values ('7566','-1') on conflict
update set count=TARGET.count + EXCLUDED.count
where TARGET.index = EXCLUDED.index
returning count

ON CONFLICT UPDATE will only ever use unique indexes, and so is not affected.

Given that exclusion constraints can only be used with IGNORE, and
given that this is so hard to recreate, I'm inclined to conclude that
it's acceptable. It's certainly way better than risking livelocks by
not having "deadlock insurance". This is a ridiculously CPU-bound
workload, with extreme and constant contention. I'd be surprised if
there were any real complaints from the field in practice.

Do you think that this is acceptable, Heikki?

[1] https://github.com/petergeoghegan/postgres/commit/c842c798e4a9e31dce06b4836b2bdcbafe1155d6#diff-51288d1b75a37ac3b32717ec50b66c23R87
--
Peter Geoghegan