Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze
От | Jerry Gamache |
---|---|
Тема | Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze |
Дата | |
Msg-id | 4B730DCE.3000809@idilia.com обсуждение исходный текст |
Ответ на | Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze (Alvaro Herrera <alvherre@commandprompt.com>) |
Список | pgsql-bugs |
Here is the pg_locks output. Alvaro Herrera wrote: > Jerry Gamache wrote: > >> I was not able to repro with default parameters, or at 15s naptime, >> and at 1s naptime I got only 1deadlock in 3 tests. >> >> This time the deadlock was with table_a, table_b and table_c >> (table_x and table_y were not involved). >> >> 18395 | database1 | autovacuum: ANALYZE public.table_a >> 18406 | database1 | autovacuum: ANALYZE public.table_b >> 18510 | database1 | >> : CREATE UNIQUE INDEX index_bg ON table_b >> USING btree (col_g); >> 18567 | database1 | autovacuum: ANALYZE public.table_c >> 18802 | database1 | select procpid,datname,current_query from >> pg_stat_activity where datname='database1' ORDER BY procpid; >> >> There is a FK constraint between table_a and table_b, but table_c >> does not have any direct constraint relation with the other 2 >> tables. >> >> The logs show that the autovacuum of table_b was canceled 20 minutes >> ago, but the thread is still alive and blocked. >> > > That's pretty strange. Can we see a pg_locks snapshot? (Please attach > as a text file so that it doesn't get word-wrapped) > > database1=# select locktype,database,relation,virtualxid,virtualtransaction,pid,mode,granted from pg_locks where pid IN (SELECTprocpid FROM pg_stat_activity WHERE datname='database1') ORDER BY pid; locktype | database | relation | virtualxid | virtualtransaction | pid | mode | granted ------------+----------+----------+------------+--------------------+-------+--------------------------+--------- relation | 20162 | 20893 | | 4/72 | 18395 | AccessShareLock | t virtualxid | | | 4/72 | 4/72 | 18395 | ExclusiveLock | t relation | 20162 | 20829 | | 4/72 | 18395 | AccessShareLock | t relation | 20162 | 20894 | | 4/72 | 18395 | AccessShareLock | t relation | 20162 | 20892 | | 4/72 | 18395 | AccessShareLock | t relation | 20162 | 20515 | | 4/72 | 18395 | ShareUpdateExclusiveLock | t relation | 20162 | 20891 | | 3/53 | 18406 | AccessShareLock | t relation | 20162 | 20813 | | 3/53 | 18406 | AccessShareLock | t relation | 20162 | 20490 | | 3/53 | 18406 | ShareUpdateExclusiveLock | t virtualxid | | | 3/53 | 3/53 | 18406 | ExclusiveLock | t relation | 20162 | 20490 | | 8/162 | 18510 | ShareLock | f virtualxid | | | 8/162 | 8/162 | 18510 | ExclusiveLock | t relation | 20162 | 20247 | | 7/238 | 18567 | ShareUpdateExclusiveLock | t relation | 20162 | 20872 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20881 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20880 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20878 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20873 | | 7/238 | 18567 | AccessShareLock | t virtualxid | | | 7/238 | 7/238 | 18567 | ExclusiveLock | t relation | 20162 | 20876 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20882 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20879 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20797 | | 7/238 | 18567 | AccessShareLock | t relation | 20162 | 20877 | | 7/238 | 18567 | AccessShareLock | t relation | 0 | 2676 | | 1/611 | 18802 | AccessShareLock | t relation | 0 | 2672 | | 1/611 | 18802 | AccessShareLock | t virtualxid | | | 1/611 | 1/611 | 18802 | ExclusiveLock | t relation | 0 | 1262 | | 1/611 | 18802 | AccessShareLock | t relation | 20162 | 11042 | | 1/611 | 18802 | AccessShareLock | t relation | 0 | 2671 | | 1/611 | 18802 | AccessShareLock | t relation | 0 | 2677 | | 1/611 | 18802 | AccessShareLock | t relation | 20162 | 10969 | | 1/611 | 18802 | AccessShareLock | t relation | 0 | 1260 | | 1/611 | 18802 | AccessShareLock | t (33 rows) database1=# select procpid,datname,current_query from pg_stat_activity where datname='database1' ORDER BY procpid; procpid | datname | current_query ---------+------------------+--------------------------------------------------------------------------------------------------------------- 18395 | database1 | autovacuum: ANALYZE public.table_a 18406 | database1 | autovacuum: ANALYZE public.table_b 18510 | database1 | : CREATE UNIQUE INDEX index_bg ON table_b USING btree (col_g); 18567 | database1 | autovacuum: ANALYZE public.table_c 18802 | database1 | select procpid,datname,current_query from pg_stat_activity where datname='database1' ORDER BY procpid; (5 rows)
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Jerry GamacheДата:
Сообщение: Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze
Следующее
От: Jerry GamacheДата:
Сообщение: Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze