"Florian G. Pflug" <fgp@xxxxxxxx> 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
