Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 810CF37BED5
	for <pgsql-hackers-postgresql.org@postgresql.org>;
	Wed,  8 Oct 2008 06:34:55 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 76722-07 for
	<pgsql-hackers-postgresql.org@postgresql.org>;
	Wed,  8 Oct 2008 06:34:51 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169])
	by postgresql.org (Postfix) with ESMTP id 389FD37BE34
	for <pgsql-hackers@postgresql.org>;
	Wed,  8 Oct 2008 06:34:50 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id 29so513203ugo.7
	for <pgsql-hackers@postgresql.org>;
	Wed, 08 Oct 2008 02:34:48 -0700 (PDT)
Received: by 10.210.72.14 with SMTP id u14mr9583030eba.179.1223458488187;
	Wed, 08 Oct 2008 02:34:48 -0700 (PDT)
Received: from ?80.222.33.27? (dsl-hkibrasgw2-fe21de00-27.dhcp.inet.fi
	[80.222.33.27])
	by mx.google.com with ESMTPS id g17sm9183121nfd.3.2008.10.08.02.34.46
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Wed, 08 Oct 2008 02:34:47 -0700 (PDT)
Message-ID: <48EC7EB5.5020001@enterprisedb.com>
Date: Wed, 08 Oct 2008 12:34:45 +0300
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.16 (X11/20080724)
MIME-Version: 1.0
To: Emmanuel Cecchet <manu@frogthinker.org>
CC: pgsql-hackers@postgresql.org
Subject: Re: Transactions and temp tables
References: <48EA7B12.6050906@frogthinker.org>
	<48EB0551.50205@enterprisedb.com> <48EB95B7.10604@frogthinker.org>
In-Reply-To: <48EB95B7.10604@frogthinker.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200810/303
X-Sequence-Number: 125320

Emmanuel Cecchet wrote:
>> Also, even if the table is created and dropped in the same 
>> transaction, a subsequent transaction that tries to create and drop 
>> the table gets blocked on the lock. I suppose we could just say that 
>> that's the way it works, but I'm afraid it will come as a nasty 
>> surprise, making the feature a lot less useful.
> I do not get that one, if the table is dropped in the transaction the 
> lock is released. Why would another transaction be blocked when trying 
> to create/drop another temp table?

I was thinking of a transaction that's just prepared (1st phase), but 
not committed or rolled back:

postgres=# CREATE TEMP TABLE foo (bar int);
CREATE TABLE
postgres=# BEGIN;
BEGIN
postgres=# DROP TABLE foo;
DROP TABLE
postgres=# PREPARE TRANSACTION '2pc';
PREPARE TRANSACTION
postgres=# SELECT * FROM foo;
<blocks>

Furthermore, it looks like the backend refuses to shut down, even if you 
end the psql session, because RemoveTempRelations() is called on backend 
shutdown and it gets blocked on that lock.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com