Tom Lane wrote:
> Emmanuel Cecchet <manu@frogthinker.org> writes:
>
>> Take PG 8.3.0 and try:
>> BEGIN;
>> CREATE TEMP TABLE foo (x int) ON COMMIT DROP;
>> PREPARE TRANSACTION 't1';
>> [BEGIN;] <-- doesn't really matter if you start a new transaction or not
>> CREATE TEMP TABLE foo (x int); <-- blocks until t1 commits
>>
>> I have been tracking down the problem and it looks like
>> PostPrepare_Locks is holding the locks on 'foo' for some reason I don't
>> really get.
>>
>
> AFAIK that doesn't really have anything to do with the temp-ness of the
> table; it'd be the same with a regular table. The problem is you have
> an in-doubt tuple in pg_class for pg_temp_NNN.foo, and you are trying
> to create another one for the same schema/relname, and so the unique
> index check is blocking to see what happens to the other transaction
> that's creating/deleting the conflicting tuple.
>
You are right (of course!), I tried:
BEGIN;
CREATE TABLE foo (x int);
DROP TABLE foo;
PREPARE TRANSACTION 't1';
[BEGIN;]
CREATE TABLE foo (x int); <-- blocks
There should not be a doubt about table foo because whether the
transaction commits or rollbacks, that table will not exist anymore (we
can get rid of it at prepare time actually).
I guess Postgres does not handle the special case of tables (temp or
not) whose lifespan is limited to the scope of a transaction and
therefore cannot optimize that case. Is that correct?
Thanks for your help.
Emmanuel