Re: Transactions and temp tables

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