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(at)frogthinker(dot)org
Skype: emmanuel_cecchet