Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 9CCBB37BA6D
	for <pgsql-hackers-postgresql.org@postgresql.org>;
	Wed,  8 Oct 2008 09:48:40 -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 24697-06 for
	<pgsql-hackers-postgresql.org@postgresql.org>;
	Wed,  8 Oct 2008 09:48:34 -0300 (ADT)
X-Greylist: delayed 00:06:38.69176 by SQLgrey-1.7.6
Received: from outbound-mail-154.bluehost.com (outbound-mail-154.bluehost.com
	[67.222.39.34]) by postgresql.org (Postfix) with SMTP id 2DFBD37BA5B
	for <pgsql-hackers@postgresql.org>;
	Wed,  8 Oct 2008 09:48:34 -0300 (ADT)
Received: (qmail 10695 invoked by uid 0); 8 Oct 2008 12:41:50 -0000
Received: from unknown (HELO fast22.fastdomain.com) (74.220.199.22)
	by outboundproxy5.bluehost.com with SMTP; 8 Oct 2008 12:41:50 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=frogthinker.org;
	h=Received:Message-ID:Date:From:Organization:User-Agent:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding:X-Identified-User;
	b=Ej0A9RKOpv3VuKlok5GIA+VlHJnnvaqnoyxPJE1XI5KsPjccuHAFK6gMZWKEU/JlUODNhvhuy8v99bi/4qSiL2186NxEh4Tvc6h3sUYAklq8IVrzLkDRFJqgwDGAQ/ni;
Received: from c-71-192-35-139.hsd1.ma.comcast.net ([71.192.35.139]
	helo=[192.168.0.14])
	by fast22.fastdomain.com with esmtpsa (TLSv1:AES256-SHA:256)
	(Exim 4.69) (envelope-from <manu@frogthinker.org>)
	id 1KnYMP-0000wD-OG; Wed, 08 Oct 2008 06:41:49 -0600
Message-ID: <48ECAA8F.3070306@frogthinker.org>
Date: Wed, 08 Oct 2008 08:41:51 -0400
From: Emmanuel Cecchet <manu@frogthinker.org>
Organization: Frog Thinker
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
CC: pgsql-hackers@postgresql.org
Subject: Re: Transactions and temp tables
References: <48EA7B12.6050906@frogthinker.org>
	<48EB0551.50205@enterprisedb.com> <48EB95B7.10604@frogthinker.org>
	<48EC7EB5.5020001@enterprisedb.com>
In-Reply-To: <48EC7EB5.5020001@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Identified-User: {2846:fast22.fastdomain.com:frogthin:frogthinker.org}
	{sentby:smtp auth 71.192.35.139 authed with
	manu+frogthinker.org}
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/311
X-Sequence-Number: 125328

Heikki Linnakangas wrote:
> 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.
Thanks for the example, I get it now. Does it make sense to allow any 
request execution between PREPARE TRANSACTION and the subsequent COMMIT 
or ROLLBACK?
I did the same experiment with a regular table (not a temp table) and it 
blocks exactly the same way, so I don't think that the problem is 
specific to temp tables.
Once PREPARE has been executed, the transaction state is restored to 
TRANS_DEFAULT, but I wonder if we should not have a specific 
TRANS_PREPARED state in which we can only authorize a COMMIT or a 
ROLLBACK. Is there any reasonable use case where someone would have to 
execute requests between PREPARE and COMMIT/ROLLBACK?

Let me know what you think of the additional TRANS_PREPARED transaction 
state. It looks like the behavior of what happens between PREPARE and 
COMMIT/ROLLBACK is pretty much undefined.

Emmanuel

-- 
Emmanuel Cecchet
FTO @ Frog Thinker 
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet