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

Re: BUG #3883: Autovacuum deadlock with truncate?

From: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3883: Autovacuum deadlock with truncate?
Date: 2008-01-17 19:25:03
Message-ID: 357fa7590801171125p26c8a041m88daabe081b84d2d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Russell SmithDate: 2008-01-17 19:26:26
Subject: Minor mathematical error in documentation
Previous:From: Steven FlattDate: 2008-01-17 19:11:10
Subject: Re: BUG #3883: Autovacuum deadlock with truncate?

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