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