Received: from localhost (unknown [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 2FC7F2E0CD9
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 13 Nov 2007 04:49:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 79945-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 13 Nov 2007 04:49:43 -0400 (AST)
Received: from pat.uio.no (pat.uio.no [129.240.10.15])
	by postgresql.org (Postfix) with ESMTP id ADF012E0FB2
	for <pgsql-performance@postgresql.org>;
	Tue, 13 Nov 2007 04:49:45 -0400 (AST)
Received: from mail-mx5.uio.no ([129.240.10.46])
	by pat.uio.no with esmtp (Exim 4.67)
	(envelope-from <r.m.guerrero@usit.uio.no>)
	id 1IrrSq-0000UX-EZ; Tue, 13 Nov 2007 09:49:44 +0100
Received: from smtp.uio.no ([129.240.10.9] helo=mail-mx5.uio.no)
	by mail-mx5.uio.no with esmtp (Exim 4.67)
	(envelope-from <r.m.guerrero@usit.uio.no>)
	id 1IrrSq-0008NP-4d; Tue, 13 Nov 2007 09:49:44 +0100
Received: from bbking.uio.no ([129.240.201.179])
	by mail-mx5.uio.no with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.67)
	(envelope-from <r.m.guerrero@usit.uio.no>)
	id 1IrrSq-0008NK-1O; Tue, 13 Nov 2007 09:49:44 +0100
Message-ID: <47396527.5090801@usit.uio.no>
Date: Tue, 13 Nov 2007 09:49:43 +0100
From: Rafael Martinez <r.m.guerrero@usit.uio.no>
User-Agent: Thunderbird 2.0.0.6 (X11/20070728)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki@enterprisedb.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Need to run CLUSTER to keep performance
References: <4732E6B4.1030907@usit.uio.no> <4732EF07.3060807@enterprisedb.com>
	<47332200.9050705@usit.uio.no> <24775.1194535980@sss.pgh.pa.us>
	<4738735B.6030408@usit.uio.no> <47387FED.8090408@enterprisedb.com>
In-Reply-To: <47387FED.8090408@enterprisedb.com>
X-Enigmail-Version: 0.95.5
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-UiO-Resend: resent
X-UiO-ClamAV-Virus: No
X-UiO-Spam-info: not spam, SpamAssassin (score=-4.7, required=12.0,
	autolearn=disabled, AWL=0.324, UIO_MAIL_IS_INTERNAL=-5)
X-UiO-Scanned: 9346EB403E8B6EC1D85EC936C9D24AEA0BCFAEE3
X-UiO-Ratelimit-Test: Ratelimit
X-UiO-SPAM-Test: UIO-RATELIMIT remote_host: 129.240.10.9 spam_score: -46
	maxlevel 200 minaction 2 bait 0 mail/h: 1522 total 5088604
	max/h 8345 blacklist 0 greylist 0 ratelimit 1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/191
X-Sequence-Number: 27606

Heikki Linnakangas wrote:
> Rafael Martinez wrote:
>> DETAIL:  83623 dead row versions cannot be removed yet.
> 
> Looks like you have a long-running transaction in the background, so
> VACUUM can't remove all dead tuples. I didn't see that in the vacuum
> verbose outputs you sent earlier. Is there any backends in "Idle in
> transaction" state, if you run ps?
> 

I don't see any long transaction in progress (<IDLE> in transaction) and
if we run the vacuum jobb manual just after checking this, it still
cannot remove the dead rows.

Any suggestions cause vacuum cannot remove these dead rows?

> In 8.1, CLUSTER will remove those tuples anyway, but it's actually not
> correct. 

With other words, .... we have to be very carefull to not run CLUSTER on
a table been modified inside a transaction if we do not want to lose
data? ...

Does this mean that if we run a transaction which update/delete many
rows, run cluster before the transaction is finnish, and then rollback
the transaction after cluster has been executed, all dead rows
updated/deleted  by the transaction can not be rollbacked back because
they are not there anymore?


-- 
 Rafael Martinez, <r.m.guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/