Temporary table retains old contents on update eventually causing slow temp file usage.

[Rusty Conover <rconover@xxxxxxxxxxxxx>]

Hi,

It would seem that doing any changes on a temp table forces a copy of  
the entire contents of the table to be retained in memory/disk. Is  
this  happening due to MVCC?  Is there a way to change this behavior?  
It could be very useful when you have really huge temp tables that  
need to be updated a few times before they can be dropped.

Below is an example of the problem.  I'll create a temp table, insert  
600 rows (just a bunch of urls, you can use anything really), then  
update the table a few times without actually changing anything.  Of  
course this test case really doesn't show the extent of the problem,  
because its such a small amount of data involved.  When I have a temp  
table of about 150 megs and do more then a few updates on it, it  
forces postgresql to use the disk making things really slow.   
Originally the entire temp table fit into RAM.

I tried using savepoints and releasing them to see if it would make  
any difference and it did not, which isn't unexpected.  Could  
pg_relation_size() be incorrect in this case?

Cheers,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com



test=# begin;
BEGIN
test=# create temp table test_urls (u text);
CREATE TABLE
test=# insert into test_urls (u) select url from url limit 600;
INSERT 0 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
            73728
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
           147456
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
           212992
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
           286720
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
           352256
(1 row)

test=# update test_urls set u = u;
UPDATE 600
test=# select pg_relation_size('test_urls');
pg_relation_size
------------------
           425984
(1 row)