Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
От | Peter Geoghegan |
---|---|
Тема | Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0 |
Дата | |
Msg-id | CAM3SWZTzKXsGkxDdxNZjJEm=3P0Eq63y8OUV5rD5cegoWK8B_A@mail.gmail.com обсуждение исходный текст |
Ответ на | INSERT ... ON CONFLICT UPDATE/IGNORE 4.0 (Peter Geoghegan <pg@heroku.com>) |
Ответы |
Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
(Peter Geoghegan <pg@heroku.com>)
Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0 (Heikki Linnakangas <hlinnaka@iki.fi>) |
Список | pgsql-hackers |
On Sun, Apr 26, 2015 at 6:02 PM, Peter Geoghegan <pg@heroku.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 speculativetoken 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 whereTARGET.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 whereTARGET.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
В списке pgsql-hackers по дате отправления: