Date: Sat, 10 Nov 2007 14:57:10 -0500
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Curious about dead rows.
In-reply-to: <20071110105119.35346a29@scratch>
To: pgsql performance <pgsql-performance@postgresql.org>
Message-id: <47360D16.7070107@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <4735DBDB.5050208@verizon.net>	<16627.1194714491@sss.pgh.pa.us>
	<4735FA9F.1080805@verizon.net> <20071110105119.35346a29@scratch>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)

Joshua D. Drake wrote:
> On Sat, 10 Nov 2007 13:38:23 -0500 Jean-David Beyer
> <jeandavid8@verizon.net> wrote:
> 
>>> Tom Lane wrote:
>>>> Jean-David Beyer <jeandavid8@verizon.net> writes:
>>>>> I am doing lots of INSERTs on a table that starts out empty (I
>>>>> did a TRUNCATE on it). I am not, AFAIK, doing DELETEs or UPDATEs.
>>>>>  Autovacuum is on. I moved logging up to debug2 level to see what
>>>>>  was going on, and I get things like this: "vl_as": scanned 3000
>>>>> of 5296 pages, containing 232944 live rows and 1033 dead rows;
>>>>> 3000 rows in sample, 411224 estimated total rows A little later,
>>>>> it says: "vl_as": scanned 3000 of 6916 pages, containing 233507
>>>>> live rows and 493 dead rows; 3000 rows in sample, 538311
>>>>> estimated total rows
>>>> Well, *something* is doing deletes or updates in that table.
>>>> Better look a bit harder at your application ...
>>>> 
>>> OK, you agree that if I am doing only INSERTs, that there should not 
>>> be any dead rows. Therefore, I _must_ be doing deletes or updates.
>>> 
>>> But the program is pretty simple, and I see no UPDATEs or DELETEs. I 
>>> searched all the program source files (that contain none of them) and
>>>  all the libraries I have written, and they have none either. Right 
>>> now the programs are not to the state where UPDATEs or DELETEs are 
>>> required (though they will be later). I am still developing them and 
>>> it is easier to just restore from backup or start over from the 
>>> beginning since most of the changes are data laundering from an 
>>> ever-increasing number of spreadsheets.
>>> 
>>> Am I right that TRUNCATE deletes all the rows of a table. They may 
>>> then be still there, but would not autovacuum clean out the dead 
>>> rows? Or maybe it has not gotten to them yet? I could do an explicit 
>>> one earlier.
> 
> Truncate will not create dead rows. However ROLLBACK will. Are you 
> getting any duplicate key errors or anything like that when you insert?
> 
On the mistaken assumption that TRUNCATE left dead rows, I did a
VACUUM FULL ANALYZE before running the program full of INSERTs. This did not
make any difference.

As far as ROLLBACK are concerned, every one is immediately preceded by a
message output to the standard error file, and no such messages are produced.

-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 14:50:01 up 18 days, 8:08, 5 users, load average: 5.23, 5.35, 5.34