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

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
Message-ID: 1172751710.3772.111.camel@coppola.muc.ecircle.de (view raw or flat)
Thread:
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.

Cheers,
Csaba.



In response to

Responses

pgsql-hackers by date

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

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