Message-ID: <49364AFB.5060404@enterprisedb.com>
Date: Wed, 03 Dec 2008 11:01:47 +0200
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.17 (X11/20081018)
MIME-Version: 1.0
To: Emmanuel Cecchet <manu@frogthinker.org>
CC: Tom Lane <tgl@sss.pgh.pa.us>, 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>
	<48ECAA8F.3070306@frogthinker.org> <28137.1223472191@sss.pgh.pa.us>
	<48ECBBC2.6060003@frogthinker.org> <29132.1223475486@sss.pgh.pa.us>
	<48EFA13C.2060407@frogthinker.org>
	<490F804A.60108@enterprisedb.com> <490FB4B3.908@frogthinker.org>
	<49106B10.2090407@enterprisedb.com>
	<4910DFC0.3080301@frogthinker.org>
	<4914CD14.2060608@frogthinker.org>
	<4921C42A.3030600@enterprisedb.com>
	<492221A3.1060608@frogthinker.org>
	<4922A798.7050709@enterprisedb.com>
	<49232993.1050801@enterprisedb.com>
	<49243376.2000404@frogthinker.org>
	<492543D5.9050904@enterprisedb.com>
	<492B377F.4080602@frogthinker.org>
	<49344E22.2000008@frogthinker.org>
	<4934A4E0.8080208@frogthinker.org>
In-Reply-To: <4934A4E0.8080208@frogthinker.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>

Emmanuel Cecchet wrote:
> There is a problem with temp tables with on delete rows that are created 
> inside a transaction.
> Take the 2pc_on_delete_rows_transaction.sql test case and change the 
> creation statement, instead of
> create temp table foo(x int) on commit delete rows;
> try
> create temp table foo(x serial primary key) on commit delete rows;
> 
> The test will fail. It looks like the onCommit field is not properly 
> updated when serial or primary key is used in that context. I did not 
> figure out why.

A serial column uses a sequence behind the scenes.

Hmm. Seems like we would need to treat sequences and indexes the same as 
tables with ON COMMIT DELETE ROWS, i.e release the locks early and don't 
error out.

All in all, this is getting pretty messy. My patch felt a bit hackish to 
begin with, and having to add special cases for sequences and indexes 
would make it even more so. And what about temporary views? I'm starting 
to feel that instead of special-casing temp relations, we need to move 
into the opposite direction and make temp relations more like regular 
relations. Unfortunately, that's not going to happen in the 8.4 
timeframe :-(. Let's try the other approach in 8.5.

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