Re: Revitalising VACUUM FULL for 8.3

On Thu, 2007-03-01 at 13:21 +0100, Csaba Nagy wrote:
> On Thu, 2007-03-01 at 13:02, Simon Riggs wrote:
> > I would like to introduce the concept of utility transactions. This is
> > any transaction that touches only one table in a transaction and is not
> > returning or modifying data. All utility transactions wait until they
> > are older than all non-utility transactions before they commit. A
> > utility transaction would currently be any VACUUM, VACUUM FULL and
> > CREATE INDEX CONCURRENTLY. That is safe because each of those commands
> > executes in its own transaction and doesn't touch more than one table at
> > a time. Once each knows there is no chance of being interfered with, it
> > can continue its work and commit. This technique is already in use for
> > CREATE INDEX CONCURRENTLY, so just needs to be extended to all other
> > utilities - but in a way that allows them to recognise each other. This
> > extends upon the thought that VACUUMs already recognise other VACUUMs
> > and avoid using them as part of their Snapshot.
>
> Wouldn't this be deadlock prone ? What if a non-utility transaction
> (which could even be started before the vacuum full) blocks on the table
> being vacuumed, then if the vacuum wants to wait until all non-utility
> transactions finish will deadlock.

Exactly the same as CREATE INDEX CONCURRENTLY, which has a much more
frequent use case than VACUUM FULL does, even after I've made the
proposed changes.

The situation, as I understand it, would be that the utility command
waits on another transaction to complete. As soon as that other
transaction touches the locked table it will detect a simple deadlock
and the non-utility statement will abort.

> > The utility transaction concept would make new VACUUM FULL MVCC-safe and
> > would also make most executions of CLUSTER MVCC-safe also (the implicit
> > top-level transaction cases).
>
> Making cluster MVCC-safe will kill my back-door of clustering a hot
> table while I run a full DB backup.

Wow. I'll take that as a request for a NOWAIT option on utility
commands, rather than a suggestion that we shouldn't strive to make
things safe in the default case.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com