Skip site navigation (1) Skip section navigation (2)

Re: Revitalising VACUUM FULL for 8.3

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Csaba Nagy" <nagy(at)ecircle-ag(dot)com>
Cc: "postgres hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Revitalising VACUUM FULL for 8.3
Date: 2007-03-01 12:56:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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             

In response to


pgsql-hackers by date

Next:From: Csaba NagyDate: 2007-03-01 13:04:06
Subject: Re: Revitalising VACUUM FULL for 8.3
Previous:From: Hannu KrosingDate: 2007-03-01 12:42:51
Subject: Re: Revitalising VACUUM FULL for 8.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group