Re: VACUUM FULL results in deadlock

On 2019-Jul-05, Robert Haas wrote:

> On Thu, Jul 4, 2019 at 9:12 AM Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com> wrote:
> > 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.
>
> I don't know whether or not this is a bug, but my guess is that it
> isn't. pg_database and several other system catalogs are shared
> relations, which means that they are shared by all databases. So, it's
> not too surprising that you could get some interaction between VACUUM
> FULL command in different databases. Routine use of VACUUM FULL is
> something that should be avoided, so it doesn't seem like a huge
> problem to me that you can't run 32 of them at the same time.

Actually, in my read of the code, VACUUM FULL is supposed to obtain a
InvalidOid-database lock of the relation being vacuumed if it's a shared
one, so it seems to me that it should work -- namely that when vacuuming
a global table, any other vacuum of that table should be blocked
regardless of what database it occurs in.

On the other hand, not all the errors that Manuel reports are obviously
related to global catalogs. For example, why is it complaining about a
missing toast value in pg_statistic's toast table? Do global tables
have statistics in specific databases' pg_statistic? How does analyze
work sensibly in that case? Maybe that's okay, but still the whole thing
should be blocked by the InvalidOid-database-level lock.

I agree that you should not be running 32 full vacuums at once, much
less in a loop, but if you do, they shouldn't result in weird corruption
such as the ones reported.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services