Re: Transactions and temp tables

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