Re: [HACKERS] DROP DATABASE and prepared xacts
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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] DROP DATABASE and prepared xacts
Heikki Linnakangas <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DROP DATABASE and prepared xacts
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] DROP DATABASE and prepared xacts
Heikki Linnakangas <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP DATABASE and prepared xacts
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
Re: [HACKERS] DROP DATABASE and prepared xacts
Alvaro Herrera <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] 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 sentarse en duras y empinadas sillas/ desprovistas, por cierto de blandos atenuantes" (Patricio Vogel) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly