Re: BUG #3883: Autovacuum deadlock with truncate?

On 1/17/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> No, that's not what the backtraces say. The autovac process is trying
> to get super-exclusive lock on a buffer (apparently in relation 16783
> --- what is that?). There's no evidence in the stack trace that the
> TRUNCATE process has any conflicting buffer lock.

Relation 16783 is a regular table, nothing special about it, except
perhaps that it's inherited? (It's a partition.) It's got an integer
primary key column whose default value is the nextval of a sequence,
another integer column, two varchar columns, and five timestamptz
columns. It's got three indexes and a check constraint.

Data would have been inserted into this table, then many rows updated
as part of a test. Immediately following the test, the data would
have been truncated by the fn_clean_tables() function. Apparently the
autovacuumer wanted to work on the table at the same time.

> What I think might be happening is a three-way deadlock involving these
> two and a third process that has the desired buffer lock. Have you got
> anything else that seems to be stuck?

Don't see a third process that is stuck...

=# select * from pg_stat_activity where current_query not like '%IDLE%';
datid | datname | procpid | usesysid | usename | current_query
| waiting | query_start | backend_sta
rt | client_addr | client_port
-------+---------+---------+----------+---------+-------------------------------
------------------+---------+------------------------------+--------------------
-----------+-------------+-------------
16384 | datname | 35775 | 10 | pgsql | VACUUM ANALYZE
public.foo | f | | 2008-01-17
01:31:54.932049-05 | |
16384 | datname | 6869 | 10 | pgsql | SELECT
fn_clean_tables() | t | 2008-01-17 01:31:51.68996-05
| 2008-01-16 22:34:40.914391-05 | 0.0.0.0 | 51451
(2 rows)

(I've obfuscated some of the names.)

Steve