Re: Revitalising VACUUM FULL for 8.3

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.

> 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.

Cheers,
Csaba.