Thread: Locks on temp table and PREPARE
Hi, As we discussed during PGCon, we are using temp tables in 2PC transactions. The temp tables are dropped before PREPARE (or have an ON COMMIT DROP option) and never cross transaction boundaries. In 8.3.1, a patch was introduced to disallow temp tables in 2PC transactions and we tried to provide a fix for it (see the long thread with Heikki on this list). I am still working on a cleaner patch to allow temp tables to be used in 2PC transactions but I did hit a new problem that I don't know how to solve cleanly. 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. Any suggestion on what should be done differently for temp tables there? Thanks, Emmanuel -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development & Consulting -- Web: http://www.frogthinker.org email: manu@frogthinker.org Skype: emmanuel_cecchet
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.
regards, tom lane
Emmanuel Cecchet <manu@frogthinker.org> writes:
> Tom Lane wrote:
>> 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.
> 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).
True, but the problem is that the tuple might still be live to (some
snapshots in) that transaction, so we can't inject a duplicate tuple
without risking confusing it. In this particular case that isn't an
issue because the transaction is done executing, but the tqual.c
rules don't know that.
regards, tom lane
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
Tom Lane wrote: > True, but the problem is that the tuple might still be live to (some > snapshots in) that transaction, so we can't inject a duplicate tuple > without risking confusing it. In this particular case that isn't an > issue because the transaction is done executing, but the tqual.c > rules don't know that. > Please excuse my ignorance. I am not sure to get how the tuple could still be live to some snapshots after the transaction has prepared. What could still happen to objects that were only visible to a transaction after it has prepared? An example would definitely help. Is it possible in Postgres for a transaction to see an object that was created inside another transaction before it commits (assuming at least 'read committed' of course)? Thanks again, Emmanuel
Emmanuel Cecchet <manu@frogthinker.org> writes:
> Tom Lane wrote:
>> True, but the problem is that the tuple might still be live to (some
>> snapshots in) that transaction, so we can't inject a duplicate tuple
>> without risking confusing it. In this particular case that isn't an
>> issue because the transaction is done executing, but the tqual.c
>> rules don't know that.
> Please excuse my ignorance. I am not sure to get how the tuple could
> still be live to some snapshots after the transaction has prepared.
Well, it couldn't be because there are no snapshots in that transaction
anymore. The problem is that the *other* transaction doesn't have a
good way to know that. It just sees an open transaction with
conflicting unique-index changes.
regards, tom lane
Emmanuel Cecchet wrote: > Tom Lane wrote: >> True, but the problem is that the tuple might still be live to (some >> snapshots in) that transaction, so we can't inject a duplicate tuple >> without risking confusing it. In this particular case that isn't an >> issue because the transaction is done executing, but the tqual.c >> rules don't know that. >> > Please excuse my ignorance. I am not sure to get how the tuple could > still be live to some snapshots after the transaction has prepared. What > could still happen to objects that were only visible to a transaction > after it has prepared? An example would definitely help. The classic example is having an open cursor that references the table. (In this case it doesn't work, but the snapshot management module and tqual.c don't know it.) If you want this to work you need to improve those modules, and who knows what else ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Tom Lane wrote:
> Emmanuel Cecchet <manu@frogthinker.org> writes:
>
>> Tom Lane wrote:
>>
>>> True, but the problem is that the tuple might still be live to (some
>>> snapshots in) that transaction, so we can't inject a duplicate tuple
>>> without risking confusing it. In this particular case that isn't an
>>> issue because the transaction is done executing, but the tqual.c
>>> rules don't know that.
>>>
>
>
>> Please excuse my ignorance. I am not sure to get how the tuple could
>> still be live to some snapshots after the transaction has prepared.
>>
>
> Well, it couldn't be because there are no snapshots in that transaction
> anymore. The problem is that the *other* transaction doesn't have a
> good way to know that. It just sees an open transaction with
> conflicting unique-index changes.
>
But when the transaction prepares, we know that. What would prevent us
to do at prepare time the same cleanup that commit does?
regards, manu (indentation (C) tom lane)
Emmanuel Cecchet <manu@frogthinker.org> writes:
> But when the transaction prepares, we know that. What would prevent us
> to do at prepare time the same cleanup that commit does?
The entire point of PREPARE is that it's *not* committed yet.
regards, tom lane
Tom Lane wrote:
> Emmanuel Cecchet <manu@frogthinker.org> writes:
>
>> But when the transaction prepares, we know that. What would prevent us
>> to do at prepare time the same cleanup that commit does?
>>
>
> The entire point of PREPARE is that it's *not* committed yet.
>
Agreed but all objects that were created and dropped in the transaction
are going to be cleaned up whether the transaction commits or rollbacks.
It seems that releasing these resources at PREPARE time would help for
these 'temporary' objects that only have a transaction scope, right?
regards, manu