Joshua D. Drake wrote: > On Sat, 10 Nov 2007 13:38:23 -0500 Jean-David Beyer > <jeandavid8@xxxxxxxxxxx> wrote: > >>> Tom Lane wrote: >>>> Jean-David Beyer <jeandavid8@xxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
