deadlock while doing VACUUM and DROP

From: Jan Urbański <j(dot)urbanski(at)students(dot)mimuw(dot)edu(dot)pl>
To: Postgres - Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: deadlock while doing VACUUM and DROP
Date: 2008-05-15 23:54:42
Message-ID: 482CCD42.1060705@students.mimuw.edu.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I got this on HEAD while doing lots of CREATE -> COPY -> DROP on table.

(...)
DEBUG: ProcessUtility
DEBUG: drop auto-cascades to toast table pg_toast.pg_toast_16774
DEBUG: drop auto-cascades to type pg_toast.pg_toast_16774
DEBUG: drop auto-cascades to index pg_toast.pg_toast_16774_index
DEBUG: drop auto-cascades to type public.text
DEBUG: drop auto-cascades to type public.text[]
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
ERROR: deadlock detected
DETAIL: Process 25423 waits for AccessExclusiveLock on relation 16386
of database 1; blocked by process 25428.
Process 25428 waits for AccessShareLock on relation 16390 of
database 1; blocked by process 25423.
Process 25423: drop table manuale ;
Process 25428: autovacuum: VACUUM ANALYZE public.manuale
HINT: See server log for query details.
STATEMENT: drop table text;
(...)

I was then able to recreate this using:

$ while true; do psql template1 < test-lock.sql; done
and in another terminal
$ while true; do psql template1 -c 'vacuum analyze text'; done

The file test-lock.sql contained:

create table text ( a text );
\copy text from stdin
... 1000 lines of random 20 character strings ...
\.
drop table text;

Almost instantly messages like the above crop up, sometimes it's
AccessExclusiveLock/ShareUpdateExclusiveLock, sometimes
AccessExclusiveLock/ShareUpdateExclusiveLock.

System:
Linux 2.6.23.9 Intel Core Duo 32bit

Configure switches:
./configure --enable-debug --enable-cassert --with-libxml --with-perl
--with-python --with-openssl --with-tcl

Cheers,
--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Neil Conway 2008-05-16 00:12:39 Re: What to do with inline warnings?
Previous Message Andrew Chernow 2008-05-15 21:18:59 Re: libpq object hooks