Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 75CB5632C66
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 29 Mar 2010 11:01:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 52325-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 29 Mar 2010 14:01:00 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from officer.gifts.ru (ws7.amber.pu.ru [195.70.195.39])
	by mail.postgresql.org (Postfix) with ESMTP id E06A163246F
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Mar 2010 11:01:00 -0300 (ADT)
Received: from officer.gifts.ru (localhost [127.0.0.1])
	by officer.gifts.ru (Postfix) with ESMTP id 7C3C95C52
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Mar 2010 18:00:58 +0400 (MSD)
Received: from [192.168.0.51] (unknown [192.168.0.51])
	by officer.gifts.ru (Postfix) with ESMTP id 5F4255C50
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Mar 2010 18:00:58 +0400 (MSD)
Message-ID: <4BB0B226.4050607@gifts.ru>
Date: Mon, 29 Mar 2010 17:59:02 +0400
From: "admin@gifts.ru" <admin@gifts.ru>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: transaction overhead  at "on commit delete rows";
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: ClamAV using ClamSMTP
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74
X-Spam-Level: 
X-Archive-Number: 201003/394
X-Sequence-Number: 38219

We have a postgres database which accessed by clients app via PL/PGSQL 
stored procedures.

For some reasons we use about 25 temp tables "on commit delete rows". It 
widely used by our SP. I can see a stramge delay at any “begin” and 
“commit”:

2010-03-09 15:14:01 MSK logrus 32102 amber LOG:  duration: 20.809 ms  
statement: BEGIN
2010-03-09 15:14:01 MSK logrus 32102 amber LOG:  duration: 0.809 ms  
statement: SELECT  empl.BL_CustomerFreeCLGet('384154676925391', '8189', 
NULL)
010-03-09 15:14:01 MSK logrus 32102 amber LOG:  duration: 0.283 ms  
statement: FETCH ALL IN "<unnamed portal 165>"; -- 
+++empl.BL_CustomerFreeCLGet+++<<21360>>
2010-03-09 15:14:01 MSK logrus 32102 amber LOG:  duration: 19.895 ms  
statement: COMMIT

The more system load and more temp table used in session, then more 
“begin” and “commit” times.
This occure only with temp table "on commit delete rows".

Test example below:

create database test;
create language plpgsql;
CREATE OR REPLACE FUNCTION test_connectionprepare(in_create 
bool,in_IsTemp bool,in_DelOnCommit bool,in_TableCount int)
  RETURNS boolean AS $$

declare
  m_count int := 50;
  m_isTemp  bool;

begin

m_count := coalesce(in_TableCount,m_count);

FOR i IN 0..m_count LOOP

if in_create then
    execute 'create ' || case when in_IsTemp then ' temp ' else ' ' end 
||' table tmp_table_'
              || i::text || '(id int,pid int,name text) '
              || case when in_DelOnCommit then ' on commit delete rows ' 
else ' ' end || ';';
else
    execute 'drop table if exists tmp_table_' || i::text ||';';
end if;

END LOOP;

  return in_create;
end;
$$  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
------------------------------------------------------------------------------

Now run pgScript:
DECLARE @I;
SET @I = 1;
WHILE @I <= 100
BEGIN

select now();

    SET @I = @I + 1;
END

It spent about 2200-2300 ms on my server.

Let's create 50 temp tables: select 
test_connectionprepare(true,true,true,100);
and run script againe. We can see 2-3 times slowing!

temp tables number - test run time:

0 - 2157-2187
10 - 2500-2704
50 - 5900-6000
100 - 7900-8000
500 - 43000+

------------------------------------------------------------------------------

Sorry for my english.

My server info:
"PostgreSQL 8.4.1 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.2.real 
(GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4), 64-bit"
Linux u16 2.6.24-24-server #1 SMP Tue Jul 7 19:39:36 UTC 2009 x86_64 
GNU/Linux
4xOpteron 16 processor cores.