Locks on temp table and PREPARE

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

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

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:

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 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.

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

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