Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 4DD556332A0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 08:46:41 -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 77547-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 11:46:27 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com
	[74.125.82.174])
	by mail.postgresql.org (Postfix) with ESMTP id E617F632499
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Mar 2010 08:46:27 -0300 (ADT)
Received: by wyb39 with SMTP id 39so1622613wyb.19
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Mar 2010 04:46:26 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:date:received:message-id
	:subject:from:to:content-type:content-transfer-encoding;
	bh=DILW73OjZptZw4Ol3wLJr6dP7Ex1Y0YrMEPGPs3PZpk=;
	b=NfKgARaFop0ft9/EypurTHr6/TqJfJIhM1k625mpGtrllg1zKzwlgfdYB0bOk8ViQa
	KRIbwtt4XtljyCOYG3M5AM3JJi5glVUrNpNxor6RqFOkv6P5sYMSiuRPAYSdsM9xZtYX
	/ELm4f8OF/dzZw2jgmmaCS8UkV8xO8hotkuZA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type
	:content-transfer-encoding;
	b=YAtPDaxdZsaYJ1FQFDf7n2/ddYA6/S570IXYfrPiNO4Km+FOS0QXmL1slrPXCLy6tH
	X1IS1qX4R8z13YxAa9Fx6T3PYeaqTimsFRPGHstLSgicpFBpEvMQ+OK49RzcqdvY16Z5
	/B3CEG682aukHBtdwcHuhBDjjChg7FlJlJWnQ=
MIME-Version: 1.0
Received: by 10.216.184.65 with HTTP; Tue, 30 Mar 2010 04:46:26 -0700 (PDT)
Date: Tue, 30 Mar 2010 15:46:26 +0400
Received: by 10.216.187.208 with SMTP id y58mr13525wem.169.1269949586368;
 Tue,
	30 Mar 2010 04:46:26 -0700 (PDT)
Message-ID: <d448ea361003300446s3f750d2fj294093419699cb36@mail.gmail.com>
Subject: temp table "on commit delete rows": transaction overhead
From: Artiom Makarov <artiom.makarov@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/393
X-Sequence-Number: 38218

Hi!
We have a postgres database which accessed by clients app via PL/PGSQL
stored procedures ( 8.4.1 on x86_64 ubuntu 8.04 server).

For some reasons we use about 25 temp tables "on commit delete rows".
It widely used by our SP.

When temp tables with "on commit delete rows" exists, I can see a
strange delay at any =93begin=94 and =93commit=94.

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 quantity in session, then
more =93begin=94 and =93commit=94 delays.


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 :=3D 50;
 m_isTemp  bool;

begin

m_count :=3D 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 =3D 1;
WHILE @I <=3D 100
BEGIN

select now();

   SET @I =3D @I + 1;
END

It spent about 2200-2300 ms on my server.

Let's create 50 temp tables: select test_connectionprepare(true,true,true,1=
00);

and run script again.

We can see 2-3 times slowing!

Here temp tables quantity vs test run time:

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

I can to suppose, that all tables are truncated before and after every
transactions. Very strange method for read only transactions!
---------------------------------------------------------------------------=
---

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/Li=
nux
4xOpteron 16 processor cores.