Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cluster and MVCC

Cluster and MVCC

From
Brad Nicholson
Date:
I just want to confirm that the cluster/MVCC issues are due to
transaction visibility.  Assuming that no concurrent access is happening
to a given table when the cluster command is issued (when takes it
visibility snapshot), it is safe to cluster that table.  Correct?
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Re: Cluster and MVCC

From
"Simon Riggs"
Date:
On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote:
> I just want to confirm that the cluster/MVCC issues are due to
> transaction visibility.  Assuming that no concurrent access is happening
> to a given table when the cluster command is issued (when takes it
> visibility snapshot), it is safe to cluster that table.  Correct?

Yes, as long as pre-existing transactions do not then access the
clustered table. If they do, rows they should have seen will now not be
visible, yet you won't get an error message to say so.

You can check this by doing something similar to...


create temporary table xids as
select transactionid from pg_stat_activity a, pg_locks l where a.procpid
= l.pid and l.transactionid is not null;

cluster ....

select 'Possible MVCC violation if ' || transactionid || ' touches
clustered table' from pg_locks where transactionid in (select
transactionid from xids);
drop table xids;


--
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com

Re: Cluster and MVCC

From
Decibel!
Date:
On Fri, Aug 10, 2007 at 06:34:03PM +0100, Simon Riggs wrote:
> On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote:
> > I just want to confirm that the cluster/MVCC issues are due to
> > transaction visibility.  Assuming that no concurrent access is happening
> > to a given table when the cluster command is issued (when takes it
> > visibility snapshot), it is safe to cluster that table.  Correct?
>
> Yes, as long as pre-existing transactions do not then access the
> clustered table. If they do, rows they should have seen will now not be
> visible, yet you won't get an error message to say so.

Don't you also need to be in a serialized transaction?
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
Attachment