X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 76F759DC833 for ; Thu, 29 Dec 2005 23:00:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54382-07 for ; Thu, 29 Dec 2005 23:00:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from candle.pha.pa.us (candle.pha.pa.us [70.90.9.53]) by postgresql.org (Postfix) with ESMTP id 9F85D9DC9C4 for ; Thu, 29 Dec 2005 23:00:08 -0400 (AST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id jBU30DW21618; Thu, 29 Dec 2005 22:00:13 -0500 (EST) From: Bruce Momjian Message-Id: <200512300300.jBU30DW21618@candle.pha.pa.us> Subject: Re: Does VACUUM reorder tables on clustered indices In-Reply-To: <29359.1135005570@sss.pgh.pa.us> To: Tom Lane Date: Thu, 29 Dec 2005 22:00:13 -0500 (EST) CC: Andrew Sullivan , PostgreSQL-development X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200512/1202 X-Sequence-Number: 78050 Tom, has this bug been addressed or documented? --------------------------------------------------------------------------- Tom Lane wrote: > Andrew Sullivan 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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073