Avoiding repeated ON COMMIT truncation for temporary tables

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Avoiding repeated ON COMMIT truncation for temporary tables
Date: 2011-03-12 01:07:53
Message-ID: 201103120107.p2C17rd22697@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Currently, if you create a temporary table with the ON COMMIT action of
DELETE ROWS, the table will truncated for every commit, whether there is
any data in the table or not.

I measured the overhead using this test:

$ (echo 'CREATE TEMPORARY TABLE TEST2 (x int);'; jot -b 'SELECT 1;'
10000) | time psql test > /dev/null
6.93 real 0.93 user 0.78 sys
$ (echo 'CREATE TEMPORARY TABLE TEST2 (x int) ON COMMIT DELETE ROWS;';
jot -b 'SELECT 1;' 10000) | time psql test > /dev/null
7.93 real 1.02 user 0.72 sys

The overhead measures 14%. Is there a simple way to avoid the repeated
truncation overhead of such cases? Is this a TODO?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2011-03-12 01:37:41 Re: pg_dump -X
Previous Message David Fetter 2011-03-12 00:14:24 Re: pg_dump -X