Обсуждение: DROP DATABASE and prepared xacts
I was just testing autovac once more, and happened to Ctrl-C the regression run in the prepared_xacts test. Upon starting Postmaster again, I see this: LOG: recovering prepared transaction 14372 LOG: recovering prepared transaction 14377 Then, I reran "make check", and got ============== dropping database "regression" ============== ERROR: database "regression" is being accessed by other users command failed: "/pgsql/install/00head/bin/psql" -X -c "DROP DATABASE IF EXISTS \"regression\"" "postgres" I think we should set things up so that prepared transactions are dropped when they concern a database being dropped. Opinions? Any takers? ;-) -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "La persona que no quería pecar / estaba obligada a sentarseen duras y empinadas sillas / desprovistas, por ciertode blandosatenuantes" (Patricio Vogel)
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. +