Re: Avoiding repeated ON COMMIT truncation for temporary tables

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 fromrepeatedly truncating the table on every commit if the
tableis alreadyempty    * 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. +