Date: Tue, 18 Jul 2006 22:22:57 +1000 (EST)
From: Gavin Sherry <swm@alcove.com.au>
To: Rusty Conover <rconover@infogears.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Temporary table retains old contents on update eventually
In-Reply-To: <891F88B2-B31D-4217-9CB9-51B23C84D89A@infogears.com>
Message-ID: <Pine.LNX.4.58.0607182214370.11554@linuxworld.com.au>
References: <891F88B2-B31D-4217-9CB9-51B23C84D89A@infogears.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 18 Jul 2006, Rusty Conover wrote:

> 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.

This is caused by our MVCC implementation. It cannot be easily changed. We
rely on MVCC for two things: concurrency and rolling back of aborted
commands. Without the latter, we couldn't support the following trivially:

template1=# create temp table bar (i int);
CREATE TABLE
template1=# begin;
BEGIN
template1=# insert into bar values(1);
INSERT 0 1
template1=# abort;
ROLLBACK
template1=# select * from bar;
 i
---
(0 rows)

It would be nice if we could special case temp tables because of the fact
that concurrency does not come into the equation but I cannot see it
happening without a generalised overwriting MVCC system.

The only alternative in the mean time is to vacuum your temporary table(s)
as part of your interaction with them.

Thanks,

Gavin