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