Skip site navigation (1) Skip section navigation (2)

Re: Temporary table retains old contents on update eventually

From: Gavin Sherry <swm(at)alcove(dot)com(dot)au>
To: Rusty Conover <rconover(at)infogears(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Temporary table retains old contents on update eventually
Date: 2006-07-18 12:22:57
Message-ID: Pine.LNX.4.58.0607182214370.11554@linuxworld.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Ron PeacetreeDate: 2006-07-18 12:32:35
Subject: Re: RAID stripe size question
Previous:From: Kapadaidakis YannisDate: 2006-07-18 09:25:44
Subject: Re: Problem with bitmap-index-scan plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group