Re: Avoiding repeated ON COMMIT truncation for temporary tables

On Mon, Mar 14, 2011 at 8:52 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Mar 11, 2011 at 8:07 PM, Bruce Momjian <bruce(at)momjian(dot)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