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=oKc3MVb9s6x9kfAvKB5RTXwJkYkajfawsRK86myxbGe1y9gH/RsfWiy25RvstrKjjclj9bTNbkbWnaW7UR96vWSXK9Xhtz7pQACb6MxV1OLiMOdAc4dFcmLdcRrsFVpP;
Message-ID: <4926D7ED.9060103@frogthinker.org>
Date: Fri, 21 Nov 2008 10:46:53 -0500
From: Emmanuel Cecchet <manu@frogthinker.org>
Organization: Frog Thinker
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
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>
In-Reply-To: <492543D5.9050904@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Heikki Linnakangas wrote:
> Emmanuel Cecchet wrote:
>> I still quite did not get what the big deal was if an ON COMMIT 
>> DELETE ROWS temp table was created inside a transaction.
>
> In case the transaction that created a temp table rolls back, the 
> table needs to be removed. Removing a temporary table belonging to 
> another backend is problematic; the local buffers in the original 
> backend need to be dropped, as well as the entry in the on commit 
> actions list.
>
>> Why the new checks you are doing in lock.c would not work with 
>> dropped temp tables? Could it be possible to drop the lock as soon as 
>> the temp table is dropped inside a transaction?
>
> If you release the lock early on a table that you drop, another 
> transactions would be free to access the table, even though it's about 
> to be dropped.
>
>> I will try to find more time to review the patch tonight.
>
> Thanks!
>
> Thinking about this whole thing yet more, I wonder if we could have a 
> more holistic approach and make temporary tables work just like 
> regular ones. The problems we've identified this far are:
>
> 1. If the prepared transaction keeps the temp table locked, the 
> backend can't exit, because the shutdown hook tries to drop all temp 
> tables.
>
> 2. When a prepared transaction that has deleted a temporary table 
> commits (or one that created one aborts), we need to drop all the 
> local buffers from the original backend's private buffer cache.
>
> 3. When a prepared transaction that has deleted a temporary table 
> commits (or one that created one aborts), we need to remove the 
> on-commit entry from the original backend's private list.
>
> Is there more? I think we can solve all the above problems:
>
> 1. Modify RemoveTempRelations so that it doesn't block if it can't 
> immediately acquire lock on the to-be-removed object. That way the 
> original backend can exit even if a prepared transaction is holding a 
> lock on a temporary object.
>
> To avoid conflict with a new backend that's assigned the same 
> backendid, divorce the temporary namespace naming from backendid so 
> that the temporary namespace name stays reserved for the prepared 
> transaction.
Is that going to cause any problem with DROP CASCADE operations or 
trying to later drop a child table if the parent table is locked? I did 
hit that issue when I tried to modify RemoveTempRelations but I was 
probably not very smart at it.
> 2. Flush and drop all local buffers on temporary tables that have been 
> created or dropped in the transaction at PREPARE TRANSACTION already.
Would there be any issue if someone was trying to use a READ_UNCOMMITTED 
isolation level to access the temp table data?
> 3. Add on-commit field to pg_class, and only keep a list of temporary 
> tables that have been accessed in the current transaction in 
> backend-private memory.
Yes, this seems doable. We will certainly have to keep a list per 
transaction id in case multiple prepared but uncommitted transactions 
have accessed different temp table on that backend.

Have you already started to code some of this?
I am looking at adding new tests to check all cases including all types 
of temp tables (normal/on delete/on drop, created inside or outside a 
transaction, prepare/postmaster stop/commit/rollback, 
inherit/index/vaccuum). That should get all the use cases covered.

Let me know what you think.
Thanks,
Emmanuel

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