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 (SELECT procpid 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)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
