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
Date: Tue, 18 Jul 2006 22:22:57 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0607182214370.11554@linuxworld.com.au> (raw)
In-Reply-To: <891F88B2-B31D-4217-9CB9-51B23C84D89A@infogears.com>
References: <891F88B2-B31D-4217-9CB9-51B23C84D89A@infogears.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: swm@alcove.com.au, rconover@infogears.com
Subject: Re: Temporary table retains old contents on update eventually
In-Reply-To: <Pine.LNX.4.58.0607182214370.11554@linuxworld.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox