Skip site navigation (1) Skip section navigation (2)

Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze

From: Jerry Gamache <jerry(dot)gamache(at)idilia(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze
Date: 2010-02-10 19:16:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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.

In response to


pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2010-02-10 19:19:24
Subject: Re: BUG #5321: Parallel restore temporarily deadlocked by autovacuum analyze
Previous:From: Heikki LinnakangasDate: 2010-02-10 18:26:47
Subject: Re: Postgr. 8.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group