DROP DATABASE and prepared xacts

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

I think we should set things up so that prepared transactions are
dropped when they concern a database being dropped. Opinions?

No. A prepared transaction is one that we have guaranteed we can commit
when the 2PC manager tells us to. Reneging on that commitment is
something the DBA can choose to do manually, but I disagree with doing
it automatically.

regards, tom lane

Alvaro Herrera wrote:

I think we should set things up so that prepared transactions are
dropped when they concern a database being dropped. Opinions?

Agreed, if you want to drop the database, you don't care about the
transactions in it anymore.

It seems straightforward to implement. We'll need a version of
FinishPreparedTransaction that takes an xid instead of a global
transaction id. Then that needs to be called at roughly the same time as
DatabaseCancelAutovacuumActivity. Preferably there isn't a wide window
between rolling back the prepared transactions and committing to
dropping the database...

I just realized that you can prepare a transaction in one database,
connect to another database in the same cluster, and issue a "COMMIT
PREPARED" there. At least NOTIFY/LISTEN gets confused when you do that,
and sends the notification to the another database, not the one where
the original transaction was running :(.

Do we consider committing a transaction from another database a feature,
and fix NOTIFY/LISTEN, or should COMMIT PREPARED throw an error if
you're not connected to the same database?

Actually, I think we should completely separate the namespaces of the
global transaction identifiers, so that you could use the same gid in
two different databases without a conflict.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Do we consider committing a transaction from another database a feature,
and fix NOTIFY/LISTEN, or should COMMIT PREPARED throw an error if
you're not connected to the same database?

Hmm ... if we were sure NOTIFY were the only sore spot I'd say fix it,
but probably safer to refuse to commit, instead.

Actually, I think we should completely separate the namespaces of the
global transaction identifiers, so that you could use the same gid in
two different databases without a conflict.

Really? They're supposed to be "global".

regards, tom lane

Tom Lane wrote:

Actually, I think we should completely separate the namespaces of the
global transaction identifiers, so that you could use the same gid in
two different databases without a conflict.

Really? They're supposed to be "global".

Well yeah, the TM should be assigning globally unique ids to every
transaction. I don't trust all the TM implementations out there, and you
could even have two different TMs stepping on each others toes, but then
again I guess it's not really our problem as long as we give a nice
error message.

The XA spec recommends assigning transaction ids using the naming rules
specified for "OSI CCR atomic action identifiers". I don't know if it's
widely used in practice.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Tom Lane wrote:

Actually, I think we should completely separate the namespaces of the
global transaction identifiers, so that you could use the same gid in
two different databases without a conflict.

Really? They're supposed to be "global".

Well yeah, the TM should be assigning globally unique ids to every
transaction. I don't trust all the TM implementations out there, and you
could even have two different TMs stepping on each others toes, but then
again I guess it's not really our problem as long as we give a nice
error message.

If we did that then it'd foreclose the possibility of committing a
prepared xact from a connection in a different DB. Even though I'm a
bit worried about whether we'd have bugs in doing such a thing, I don't
really want to define it to be impossible. "Not implemented" is a lot
different from "impossible because of bad system design".

regards, tom lane

Added to TODO:

* Improve failure message when DROP DATABASE is used on a database that
has prepared transactions

---------------------------------------------------------------------------

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

I think we should set things up so that prepared transactions are
dropped when they concern a database being dropped. Opinions?

Agreed, if you want to drop the database, you don't care about the
transactions in it anymore.

It seems straightforward to implement. We'll need a version of
FinishPreparedTransaction that takes an xid instead of a global
transaction id. Then that needs to be called at roughly the same time as
DatabaseCancelAutovacuumActivity. Preferably there isn't a wide window
between rolling back the prepared transactions and committing to
dropping the database...

I just realized that you can prepare a transaction in one database,
connect to another database in the same cluster, and issue a "COMMIT
PREPARED" there. At least NOTIFY/LISTEN gets confused when you do that,
and sends the notification to the another database, not the one where
the original transaction was running :(.

Do we consider committing a transaction from another database a feature,
and fix NOTIFY/LISTEN, or should COMMIT PREPARED throw an error if
you're not connected to the same database?

Actually, I think we should completely separate the namespaces of the
global transaction identifiers, so that you could use the same gid in
two different databases without a conflict.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +