Обсуждение: Cluster and MVCC

Поиск
Список
Период
Сортировка

Cluster and MVCC

От
Brad Nicholson
Дата:
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

От
"Simon Riggs"
Дата:
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

От
Decibel!
Дата:
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)
Вложения