Re: Alternative for vacuuming queue-like tables

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> The general problem seems to be that a transaction has no way to promise
> never to touch a specific table. Maybe some kind of "negative lock"
> would help here - you'd do "exclude table foo from transaction" at the
> start of your transaction, which would cause postgres to raise an error
> if you indeed tried to access that table. Vacuum could then ignore your
> transaction when deciding which tuples it can safely remove from the
> table foo.

Unfortunately that really wouldn't help VACUUM at all. The nasty
problem for VACUUM is that what it has to figure out is not the oldest
transaction that it thinks is running, but the oldest transaction that
anyone else thinks is running. So what it looks through PGPROC for is
not the oldest XID, but the oldest XMIN. And even if it excluded procs
that had promised not to touch the target table, it would find that
their XIDs had been factored into other processes' XMINs, resulting
in no improvement.

As a comparison point, VACUUM already includes code to ignore backends
in other databases (if it's vacuuming a non-shared table), but it turns
out that that code is almost entirely useless :-(, because those other
backends still get factored into the XMINs computed by backends that are
in the same database as VACUUM. We've speculated about fixing this by
having each backend compute and advertise both "global" and "database
local" XMINs, but the extra cycles that'd need to be spent in *every*
snapshot computation seem like a pretty nasty penalty. And the approach
certainly does not scale to anything like per-table exclusions.

regards, tom lane