From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Does VACUUM reorder tables on clustered indices
Date: Mon, 19 Dec 2005 10:19:30 -0500
Message-ID: <29359.1135005570@sss.pgh.pa.us> (raw)
In-Reply-To: <20051219132524.GA6777@phlogiston.dyndns.org>
References: <1134940325.43a5d0a5a882e@webmail.adontendev.net>
<Pine.LNX.4.61.0512181840350.20029@bugs.unl.edu.ar>
<20051218220139.GA1651@surnet.cl>
<13769.1134961702@sss.pgh.pa.us>
<20051219132524.GA6777@phlogiston.dyndns.org>
Andrew Sullivan <ajs@crankycanuck.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, ajs@crankycanuck.ca
Subject: Re: Does VACUUM reorder tables on clustered indices
In-Reply-To: <29359.1135005570@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox