Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze - Mailing list 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)