Re: Locks on temp table and PREPARE

Tom Lane wrote:
> Emmanuel Cecchet <manu(at)frogthinker(dot)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