Avoiding repeated ON COMMIT truncation for temporary tables

On Fri, Mar 11, 2011 at 8:07 PM, Bruce Momjian <bruce@momjian.us> wrote:

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?

We might be able to make PreCommit_on_commit_actions() exit quickly
without doing anything if MyXactAccessedTempRel is false. I haven't
tested that solution and am not 100% confident that it's safe, but if
it is I believe it would address your concern.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Mar 14, 2011 at 8:52 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Mar 11, 2011 at 8:07 PM, Bruce Momjian <bruce@momjian.us> wrote:

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?

We might be able to make PreCommit_on_commit_actions() exit quickly
without doing anything if MyXactAccessedTempRel is false.  I haven't
tested that solution and am not 100% confident that it's safe, but if
it is I believe it would address your concern.

aside: I find 'on commit drop' tables to be quite useless, especially
in plpgsql due to performance issues. it's much better to organize
data around a permanent table organized around txid_current(), with
something swooping along periodically and cleaning it up.

merlin

Robert Haas wrote:

On Fri, Mar 11, 2011 at 8:07 PM, Bruce Momjian <bruce@momjian.us> wrote:

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?

We might be able to make PreCommit_on_commit_actions() exit quickly
without doing anything if MyXactAccessedTempRel is false. I haven't
tested that solution and am not 100% confident that it's safe, but if
it is I believe it would address your concern.

Added to TODO:

Prevent temporary tables created with ON COMMIT DELETE ROWS from
repeatedly truncating the table on every commit if the table is already
empty

* http://archives.postgresql.org/pgsql-hackers/2011-03/msg00842.php

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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