From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Spiegelberg <gspiegelberg@gmail.com>
Cc: Andy Colson <andy@squeakycode.net>
Cc: sthomas@peak6.com, "Reuven M. Lerner" <reuven@lerner.co.il>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Marcin Mańk <marcin.mank@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
Date: Thu, 23 Feb 2012 13:30:32 -0500
Message-ID: <3023.1330021832@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEtnbpWh-ko=4mznWLdPVT7pifaGMeoy0wDB1=mBcrUx35Dkdw@mail.gmail.com>
References: <4F45FB55.1020600@lerner.co.il>
<CAK61fk6bSQtmDUzm2rNv33kN4Oar_dYZ-yFEa9zR_zLL8L9esQ@mail.gmail.com>
<4F464C49.4050503@dunslane.net>
<4F465A7A.4000905@lerner.co.il>
<CAEtnbpUZkdg-gH_AcbTMG41yN+Y0ECXdtnGTdf232B6T3v_1SA@mail.gmail.com>
<4F467FCD.8090100@peak6.com>
<4F468140.2090704@squeakycode.net>
<CAEtnbpWh-ko=4mznWLdPVT7pifaGMeoy0wDB1=mBcrUx35Dkdw@mail.gmail.com>
Greg Spiegelberg <gspiegelberg@gmail.com> writes:
> I used LOCK simply because if a VACUUM FULL x; slipped in between the
> SELECT and the DELETE the ctid's could conceivably change.
VACUUM FULL can't "slip in" there, because you'd have AccessShareLock
just from the SELECT. The real problem goes like this:
1. You SELECT some ctid and save it in the other table.
2. Somebody else updates or deletes that row.
3. Plain VACUUM comes along and frees the dead TID.
4. Somebody else (maybe not same somebody as #2) inserts a new
row at that TID position.
5. You DELETE that TID. Ooops.
So you might say "okay, the point of the lock is to block plain vacuum,
not vacuum full". I'm still a bit worried about whether the technique
is entirely safe, though, because of page pruning which can happen
anyway. What this really boils down to is: how sure are you that no
other userland activity is going to update or delete any of the targeted
rows between the SELECT INTO and the DELETE? If you're sure, then this
is safe without the extra lock. Otherwise, I wouldn't trust it.
It might be worth having the SELECT that creates the temp table be a
SELECT FOR UPDATE on the target table, so as to ensure you've locked
down the targeted rows against anybody else. This is not free though,
as it'll mean extra writes of all the modified tuples.
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, gspiegelberg@gmail.com, andy@squeakycode.net, reuven@lerner.co.il, andrew@dunslane.net, marcin.mank@gmail.com
Subject: Re: Very long deletion time on a 200 GB database
In-Reply-To: <3023.1330021832@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