VACUUM FULL results in deadlock

From: Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: VACUUM FULL results in deadlock
Date: 2019-06-29 15:51:06
Message-ID: CA+u7OA6pL+7Xm_NXHLenxffe3tCr3gTamVdr7zPjcWqW0RFM-A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi everyone,

When executing multiple threads that execute VACUUM FULL on distinct
databases, a deadlock like the following can occur:

ERROR: deadlock detected
Detail: Process 16407 waits for AccessShareLock on relation 1260 of
database 0; blocked by process 16404.
Process 16404 waits for RowExclusiveLock on relation 1214 of database
0; blocked by process 16407.
Hint: See server log for query details.

This is unexpected, because the documentation does not mention that
VACUUM FULL can result in a deadlock. Also, VACUUM without a table
argument should affect only the current database [1]:

"Without a table_and_columns list, VACUUM processes every table and
materialized view in the current database that the current user has
permission to vacuum."

To reproduce such a deadlock, I've attached a Java program that first
creates 32 databases (test0 to test31), and then starts 32 threads,
each one connecting to one of the databases (with superuser
privileges). Every thread then repeatedly executes "VACUUM FULL".
Within a few seconds, deadlock error messages should pop up.

I'm using the following Postgres version: psql (11.4 (Ubuntu
11.4-1.pgdg19.04+1)).

Is this a bug?

Best,
Manuel

[1] https://www.postgresql.org/docs/11/sql-vacuum.html

Attachment Content-Type Size
ExecutePostgresVacuumDeadlock.java text/x-java 1.3 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Noah Misch 2019-06-29 21:03:34 Re: BUG #15121: Multiple UBSAN errors
Previous Message Juan José Santamaría Flecha 2019-06-29 06:19:18 Re: BUG #15858: could not stat file - over 4GB