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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq