On Jul 18, 2006, at 6:22 AM, Gavin Sherry wrote:
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.
I forgot to add in my original post that the temporary tables I'm
dealing with have the "on commit drop" flag, so really persisting
beyond the transaction isn't needed. But I don't think that makes
any difference, because of savepoints' required functionality.
The problem with vacuuming is that you can't do it by default right
now inside of a transaction.
Reading vacuum.c though, it leaves the door open:
/*
* We cannot run VACUUM inside a user transaction block; if we were
inside
* a transaction, then our commit- and start-transaction-command calls
* would not have the intended effect! Furthermore, the forced
commit that
* occurs before truncating the relation's file would have the
effect of
* committing the rest of the user's transaction too, which would
* certainly not be the desired behavior. (This only applies to VACUUM
* FULL, though. We could in theory run lazy VACUUM inside a
transaction
* block, but we choose to disallow that case because we'd rather
commit
* as soon as possible after finishing the vacuum. This is
mainly so that
* we can let go the AccessExclusiveLock that we may be holding.)
*
* ANALYZE (without VACUUM) can run either way.
*/
Since we're dealing with a temporary table we shouldn't have any
problems with the AccessExclusiveLock. Would lazy vacuuming mark the
pages as free? I assume it wouldn't release them or shrink the size
of the relation, but could they be reused on later updates in that
same transaction?
Cheers,
Rusty
--
Rusty Conover
InfoGears Inc.
Web: http://www.infogears.com
