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

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2011-03-12 01:37:41
Subject: Re: pg_dump -X
Previous:From: David FetterDate: 2011-03-12 00:14:24
Subject: Re: pg_dump -X

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