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.
Alvaro Herrera wrote:
> Jerry Gamache wrote:
>
>> I was also surprised that table_y seemed to be involved. This is not
>> a typo. Might be caused by a FK constraint between table_y and
>> table_x. From the logs, the autovacuum on table_x was canceled
>> before the one on table_y, but the restore only resumed after the
>> autovacuum on table_y was canceled. It is possible (but I cannot
>> confirm) that the autovacuum thread on table_x was blocked for a
>> while after the cancellation message was written to the log. I added
>> timestamps to log_line_prefix to be able to give more details if
>> this happens again.
>>
>
> Could you try to restore the whole dump again and see if it you can
> reproduce it? Maybe decreasing autovacuum_naptime makes it more
> probable.
>
>
