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

Re: Does VACUUM reorder tables on clustered indices

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: Does VACUUM reorder tables on clustered indices
Date: 2005-12-30 03:00:13
Message-ID: 200512300300.jBU30DW21618@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Tom, has this bug been addressed or documented?

---------------------------------------------------------------------------

Tom Lane wrote:
> Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> > On Sun, Dec 18, 2005 at 10:08:22PM -0500, Tom Lane wrote:
> >> Just for the record, that behavior is seriously broken: it violates
> >> MVCC if any of the deleted tuples are still visible to anyone else.
> 
> > Does it remove tuples that VACUUM FULL wouldn't?
> 
> Yes.  CLUSTER works on SnapshotNow, so it will remove committed-dead
> tuples even if there are still open transactions that could see them.
> Of course, said transactions couldn't be actively using the table
> while the CLUSTER runs, because it takes an exclusive table lock.
> But they *could* look at it afterwards.  Offhand I think you'd only
> be likely to notice the difference if the open transactions were
> SERIALIZABLE --- in READ COMMITTED mode, by the time they could look
> at the clustered table, they'd likely be using a snapshot that postdates
> the DELETE.
> 
> [ experiments a bit... ]  Hmm.  Actually, it's far worse than I
> thought.  It looks like CLUSTER puts the tuples into the new table with
> its own xid, which means that concurrent serializable transactions will
> see the new table as completely empty!
> 
> << session 1 >>
> 
> regression=# select * from int4_tbl;
>      f1
> -------------
>            0
>       123456
>      -123456
>   2147483647
>  -2147483647
> (5 rows)
> 
> regression=# create index fooi on int4_tbl(f1);
> CREATE INDEX
> regression=# begin isolation level serializable;
> BEGIN
> regression=# select 2+2; -- establish transaction snapshot
>  ?column?
> ----------
>         4
> (1 row)
> 
> << session 2 >>
> 
> regression=# delete from int4_tbl where f1 = -123456;
> DELETE 1
> regression=# cluster fooi on int4_tbl;
> CLUSTER
> 
> << back to session 1 >>
> 
> regression=# select * from int4_tbl;
>  f1
> ----
> (0 rows)
> 
> regression=# commit;
> COMMIT
> regression=# select * from int4_tbl;
>      f1
> -------------
>  -2147483647
>            0
>       123456
>   2147483647
> (4 rows)
> 
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-12-30 03:14:53
Subject: Re: Does VACUUM reorder tables on clustered indices
Previous:From: Bruce MomjianDate: 2005-12-30 02:50:08
Subject: Re: [HACKERS] status of concurrent VACUUM patch ...

pgsql-sql by date

Next:From: Tom LaneDate: 2005-12-30 03:14:53
Subject: Re: Does VACUUM reorder tables on clustered indices
Previous:From: Michael FuhrDate: 2005-12-29 18:46:39
Subject: Re: Arrays in PL/pgSQL routines?

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