Re: VACUUM FULL results in deadlock

Hi everyone,

Did anyone try to verify this? I found a number of other race
conditions where VACUUM causes errors such as "ERROR: found unexpected
null value in index "i0", "invalid input syntax for type boolean", or
"missing chunk number 0 for toast value 13171 in pg_toast_2619".
Fixing this would help me to narrow down these issues.

Best,
Manuel

On Sat, Jun 29, 2019 at 5:51 PM Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com> wrote:
>
> 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