From: Gregory Stark <stark@enterprisedb.com>
To: Pomarede Nicolas <npomarede@corp.free.fr>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
Date: Tue, 08 May 2007 11:50:58 +0100
Message-ID: <87sla7zizx.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0705081214250.22289@localhost>
References: <Pine.LNX.4.64.0705081125140.20675@localhost>
<46404BEA.5060009@enterprisedb.com>
<Pine.LNX.4.64.0705081214250.22289@localhost>
"Pomarede Nicolas" <npomarede@corp.free.fr> writes:
> Yes, I already do this on another spool table ; I run a vacuum after processing
> it, but I wondered if there was another way to keep the disk size low for this
> table.
"after processing it" might be too soon if there are still transactions around
that are a few minutes old and predate you committing after processing it.
But any table that receives as many deletes or updates as these tables do will
need to be vacuumed on the order of minutes, not days.
>> It should work, just like you describe it, with the caveat that TRUNCATE will
>> remove any old row versions that might still be visible to an older
>> transaction running in serializable mode.
>
> Shouldn't locking the table prevent this ? I mean, if I try to get an exclusive
> lock on the table, shouldn't I get one only when there's no older transaction,
> and in that case I can truncate the table safely, knowing that no one is
> accessing it due to the lock ?
It would arise if the transaction starts before you take the lock but hasn't
looked at the table yet. Then the lock table succeeds, you truncate it and
commit, then the old transaction gets around to looking at the table.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
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: stark@enterprisedb.com, npomarede@corp.free.fr, heikki@enterprisedb.com
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
In-Reply-To: <87sla7zizx.fsf@oxford.xeocode.com>
* 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