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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
