To: Kurt Overberg <kurt@hotdogrecords.com>
cc: Chris Browne <cbbrowne@acm.org>, pgsql-performance@postgresql.org
Subject: Re: Maintenance question / DB size anomaly... 
In-reply-to: <A0763165-9B49-42C7-BBE7-4A670D74B7EE@hotdogrecords.com> 
References: <A6D35557-74A3-47CF-B426-143C8CE01A13@hotdogrecords.com>
	<607iq0dlns.fsf@dba2.int.libertyrms.com>
	<1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com>
	<16441.1182288819@sss.pgh.pa.us>
	<A0763165-9B49-42C7-BBE7-4A670D74B7EE@hotdogrecords.com>
Comments: In-reply-to Kurt Overberg <kurt@hotdogrecords.com>
	message dated "Tue, 19 Jun 2007 18:24:21 -0400"
Date: Tue, 19 Jun 2007 19:26:10 -0400
Message-ID: <18495.1182295570@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kurt Overberg <kurt@hotdogrecords.com> writes:
> That's the thing thats kinda blowing my mind here, when I look at  
> that table:

> db1=# select count(*) from _my_cluster.sl_log_1 ;
> count
> -------
>    6788
> (1 row)

Well, that's real interesting.  AFAICS there are only two possibilities:

1. VACUUM sees the other 300k tuples as INSERT_IN_PROGRESS; a look at
the code shows that these are counted the same as plain live tuples,
but they'd not be visible to other transactions.  I wonder if you could
have any really old open transactions that might have inserted all those
tuples?

2. The other 300k tuples are committed good, but they are not seen as
valid by a normal MVCC-aware transaction, probably because of
transaction wraparound.  This would require the sl_log_1 table to have
escaped vacuuming for more than 2 billion transactions, which seems a
bit improbable but maybe not impossible.  (You did say you were running
PG 8.0.x, right?  That's the last version without any strong defenses
against transaction wraparound...)

The way to get some facts, instead of speculating, would be to get hold
of the appropriate version of pg_filedump from
http://sources.redhat.com/rhdb/ and dump out sl_log_1 with it
(probably the -i option would be sufficient), then take a close look
at the tuples that aren't visible to other transactions.  (You could
do "select ctid from sl_log_1" to determine which ones are visible.)

			regards, tom lane