Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
От | Heikki Linnakangas |
---|---|
Тема | Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0 |
Дата | |
Msg-id | 553EFF0D.4040903@iki.fi обсуждение исходный текст |
Ответ на | Re: 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>)
|
Список | pgsql-hackers |
On 04/27/2015 07:02 PM, Peter Geoghegan wrote: > 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? I thought we had an ironclad scheme to prevent deadlocks like this, so I'd like to understand why that happens. - Heikki
В списке pgsql-hackers по дате отправления:
Следующее
От: Abhijit Menon-SenДата:
Сообщение: Re: basebackups during ALTER DATABASE ... SET TABLESPACE ... not safe?