Re: Revitalising VACUUM FULL for 8.3

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Revitalising VACUUM FULL for 8.3
Date: 2007-03-01 12:21:50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2007-03-01 12:32:24 Re: Revitalising VACUUM FULL for 8.3
Previous Message Vishal Arora 2007-03-01 12:20:09 Re: POSTGRES WAL