Re: Missing CONCURRENT VACUUM (Was: Release notes for

On K, 2005-08-17 at 14:48 -0400, Tom Lane wrote:
> Hannu Krosing <hannu(at)skype(dot)net> writes:
> > Please check the actual patch and advise if anything is still missing.
>
> While testing this I realized that it does not in fact work as
> advertised. It will only exclude long-running VACUUMs from other
> VACUUMs' OldestXmin if *all* the transactions in the system are lazy
> VACUUMs. If there is even one regular transaction in the system,
> that transaction will include the VACUUMs in its MyProc->xmin, and
> thence GetOldestXmin will have to include them in its result.

Only if these regular transactions are running in SERIALIZABLE isolation
level, else MyProc->xmin is not set inside GetSnapshotData. As my
transactions mostly run in READ COMMITTED mode I did not care about
those and later forgot about it.

> AFAICS the only way to fix this would be to exclude inVacuum
> transactions from GetSnapshotData's calculations as well. That
> makes the patch far more invasive, and I'm not confident I can work
> out all the implications. (In particular, the consequences for
> TransactionIdIsInProgress look bad. I don't think we want a VACUUM
> to be seen as not-in-progress.)
>
> So I'm bouncing this patch again...

Please don't.

Even with current functionality it is part of solving the problem of
being able to vacuum small tables while vacuuming big ones at the same
time. In a scenario, where I use it, there are lot of OLTP (30-50ms)
transactions. These are run in READ COMMITTED mode, but even if I needed
them to be in SERIALIZABLE mode (or my reasoning about MyProc->xmin is
wrong), I can find (or force if needed) a few ms window where no other
transaction is running to start my VACUUM LAZY in a mode it can actually
clean up the tables.

This is still much better than not being able to do it at all. I'm ready
to write the documentation explaining it all in detail to those really
needing it.

--
Hannu Krosing <hannu(at)skype(dot)net>