Re: Does VACUUM reorder tables on clustered indices

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