Re: [HACKERS] Nested Transaction TODO list
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: [shrug...] I'd counsel leaving this as-is. What information are we loosing by having START and BEGIN use the same nodes? Knowing what keyword they used to start the transaction? Exactly. Seems that would only be important if we wanted them to behave differently, which we don't, I think. Whether we want them to behave differently or not, we need to preserve the difference. The prior cases where the parser smashed two different inputs into the same parse tree have all been because it doesn't matter, and sure enough we've usually eventually decided it did matter. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transaction TODO list
On Sat, Jul 03, 2004 at 11:03:33AM -0400, Tom Lane wrote: than begin/commit for subxacts? What about savepoints?) Also, what about exposing this functionality in plpgsql? Seems like we need some kind of exception handling syntax to make this useful. What does Oracle do? Oracle uses savepoints: SAVEPOINT savepointname; creates a savepoint or shifts existing savepoint of the same name; ROLLBACK TO savepointname; rolls back to savepoint (more verbose syntax also available); The syntax of handling exceptions is (in PL/SQL): BEGIN some code, for example a bunch of SQL commands; EXCEPTION WHEN nameofexception THEN handle the exception, maybe ROLLBACK; END; There are predefined exceptions like INVALID_NUMBER, NO_DATA_FOUND, ZERO_DIVIDE, or OTHERS. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transaction TODO list
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Why does START have a different Node from BEGIN anyway? This seems to be a leftover from when people thought they should behave differently. They are the same now, so there's no point in distinguishing them, or is it? [shrug...] I'd counsel leaving this as-is. We've practically always regretted it when we made the parser discard information about what the user typed. For instance, I was just reminded yesterday that we really ought to distinguish SortClauses created due to user ORDER BY clauses from those created because the parser silently added 'em. What information are we loosing by having START and BEGIN use the same nodes? Knowing what keyword they used to start the transaction? Seems that would only be important if we wanted them to behave differently, which we don't, I think. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] Nested Transaction TODO list
On Sat, Jul 03, 2004 at 11:03:33AM -0400, Tom Lane wrote: TransactionIdIsInProgress needs work/review; comments are off for one thing, and it seems *way* too inefficient. Note it should be possible to skip subtrans search for sufficiently old xacts (hm ... couldn't we skip sinval search too...) I am looking at this now ... the first thing I did was stamp at the start of the function if (TransactionIdPrecedes(xid, RecentGlobalXmin)) return false; So we don't need to check pg_subtrans (nor the PGPROC array) for any transaction that is too old. Now, I'm looking at adding the array of cached Xids and I think that maybe this is not the cure to all the performance problems introduced. With the cached Xid array we can return quickly for a transaction that is part of any of the current subtransaction trees, but there's no way to know about negative hits! So any time a Xid that's not part of any transaction tree is sought, we'd have to revert to pg_subtrans. This includes an aborted subtransaction of a current transaction tree. So how about adding two arrays to PGPROC: the cached Xid array (for subcommitted subxacts) and another which would hold aborted Xids? We would store Xids of aborted subxacts of the current transaction. (Maybe we could store Xids of previous transactions of this backend too?) Instead of the second array, we could have a global TransactionId array which would hold past transactions and aborted subtransactions for all backends. At AbortTransaction() and AbortSubTransaction() we would save the Xid there, using a round-robin scheme. What do you think? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No single strategy is always right (Unless the boss says so) (Larry Wall) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transaction TODO list
Tom Lane wrote: Still need to agree about externally visible behavior (a different stmt than begin/commit for subxacts? What about savepoints?) Also, what about exposing this functionality in plpgsql? Seems like we need some kind of exception handling syntax to make this useful. What does Oracle do? As I just mentioned in another thread, whatever the syntax for nested transactions I'd like to see plain COMMIT/ABORT/ROLLBACK always affect the top-level transaction. Oracle appears to have: SAVEPOINT savepointname ROLLBACK [WORK] [TO [SAVEPOINT] savepointname] You can issue SAVEPOINT with the same name while the old savepoint is valid, and the name will be moved. Rolling back to a savepoint does not invalidate that savepoint, i.e. you can roll back to a savepoint multiple times. One generalization of this to nested transactions would be: SUBBEGIN [transactionname] SUBCOMMIT [transactionname] SUBABORT [transactionname] SUBBEGIN outside an explicit transaction block works like BEGIN. Active transactions may have names. SUBBEGIN with a name associates the name with the new transaction; if the name is already in use, it's also removed from the old transaction. Alternatively we could only look at the most-deeply-nested transaction with a given name when specifying transactions by name. That would make savepoint behaviour slightly different to Oracle (Oracle could see a savepoint as invalid that we consider valid), but it looks like it'd make things a bit easier for procedural languages as functions can't accidentally trash a name belonging to your caller so long as they resolve all transactions they start. SUBCOMMIT or SUBABORT work on the current transaction level (if no name is specified) or all transactions down to (and including) the named transaction level if a name is given. SAVEPOINT savepointname becomes an alias for SUBBEGIN savepointname. ROLLBACK TO [SAVEPOINT] savepointname becomes an alias for SUBABORT savepointname; SUBBEGIN savepointname. We could spell SUBBEGIN and friends differently -- is it better to add more syntax to the existing transaction manipulation commands along the lines of BEGIN [NESTED] [TRANSACTION|WORK] [transactionname], ROLLBACK [NESTED] [TRANSACTION|WORK] [transactionname] etc? Any comments? -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
On Mon, Jul 05, 2004 at 03:38:13PM +1200, Oliver Jowett wrote: As I just mentioned in another thread, whatever the syntax for nested transactions I'd like to see plain COMMIT/ABORT/ROLLBACK always affect the top-level transaction. Oracle appears to have: SAVEPOINT savepointname ROLLBACK [WORK] [TO [SAVEPOINT] savepointname] Right ... this is also what the standard defines. A slight difference from your description is that if one issues ROLLBACK TO savepointname everything from the savepoint is rolled back, but the savepoint itself is kept, so later I can roll back to it again. One generalization of this to nested transactions would be: SUBBEGIN [transactionname] SUBCOMMIT [transactionname] SUBABORT [transactionname] The only departure from the SAVEPOINT syntax is that you are able to subcommit a savepoint. Not sure how useful that is ... Active transactions may have names. SUBBEGIN with a name associates the name with the new transaction; if the name is already in use, it's also removed from the old transaction. Alternatively we could only look at the most-deeply-nested transaction with a given name when specifying transactions by name. Interesting idea ... it's also easier to implement. Also maybe it can be used to simplify life for PL handlers aborting a function. We could spell SUBBEGIN and friends differently -- is it better to add more syntax to the existing transaction manipulation commands along the lines of BEGIN [NESTED] [TRANSACTION|WORK] [transactionname], ROLLBACK [NESTED] [TRANSACTION|WORK] [transactionname] etc? Not sure. I already implemented SUBBEGIN. How does that work for everyone? I don't see much value in overloading BEGIN/ROLLBACK. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El día que dejes de cambiar dejarás de vivir ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transaction TODO list
On Sat, Jul 03, 2004 at 11:03:33AM -0400, Tom Lane wrote: Here's the promised list of things I think we still need to fix to complete the nested-transactions project. I assume you have a private todo list as well --- can we compare notes? Hmm ... there are a lot of things in your list not in mine. The things that I have not in yours is pretty short: - fix SPI to work on functions not-in-xact-block with TransactionStmt (this is related to the item on subxacts under TBLOCK_STARTED) - fix large objects longevity Some comments: Still need to agree about externally visible behavior (a different stmt than begin/commit for subxacts? What about savepoints?) Also, what about exposing this functionality in plpgsql? Seems like we need some kind of exception handling syntax to make this useful. What does Oracle do? We should offer the savepoint syntax; seems easy to do. I think a lot of things are easier to do if we use a different syntax _and_ allow a subxact to start from TBLOCK_STARTED. What about reporting transaction state/nesting level to client? I did not like the GUC-variable approach in the original patch, partly on grounds of efficiency and partly because I doubt it works under transaction-failure conditions. I'm inclined to think we need a small protocol change. Perhaps it would work to add an additional field to Z messages that is only sent when nest level 1. It's a shame to have to lose backwards compatibility. Why can't we use ParameterStatus? Perhaps having it as a GUC var was a bad idea, but we can do otherwise. Why does postgres.c discriminate against START TRANSACTION where it now allows BEGIN? Could simplify code by just allowing all TransactionStmt types. Oversight. I wonder whether we shouldn't rename TopTransactionContext. Any existing references to TopTransactionContext are more than likely wrong given the way its semantics have been subtly changed. (I checked everything in standard distro, but what about external PLs and user-written fns ...) We need to think about this and interaction with EOXact callbacks -- a non-subxact-aware function could easily break if called inside a subxact. TransactionIdIsInProgress needs work/review; comments are off for one thing, and it seems *way* too inefficient. Note it should be possible to skip subtrans search for sufficiently old xacts (hm ... couldn't we skip sinval search too...) Can we use a cutoff like RecentGlobalXmin here? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No reniegues de lo que alguna vez creíste ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
Alvaro Herrera [EMAIL PROTECTED] writes: On Sat, Jul 03, 2004 at 11:03:33AM -0400, Tom Lane wrote: What about reporting transaction state/nesting level to client? I did not like the GUC-variable approach in the original patch, partly on grounds of efficiency and partly because I doubt it works under transaction-failure conditions. I'm inclined to think we need a small protocol change. Perhaps it would work to add an additional field to Z messages that is only sent when nest level 1. It's a shame to have to lose backwards compatibility. I don't like using ParameterStatus because it's not designed for dealing with values that may change many times in a single query. Also it sends strings, which this really isn't. I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. You could not directly tell the depth of your subxact from this, but do you need to? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transaction TODO list
Alvaro Herrera [EMAIL PROTECTED] writes: Why does START have a different Node from BEGIN anyway? This seems to be a leftover from when people thought they should behave differently. They are the same now, so there's no point in distinguishing them, or is it? [shrug...] I'd counsel leaving this as-is. We've practically always regretted it when we made the parser discard information about what the user typed. For instance, I was just reminded yesterday that we really ought to distinguish SortClauses created due to user ORDER BY clauses from those created because the parser silently added 'em. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
On Sat, 03 Jul 2004 17:40:23 -0400, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Why does START have a different Node from BEGIN anyway? This seems to be a leftover from when people thought they should behave differently. They are the same now, so there's no point in distinguishing them, or is it? [shrug...] I'd counsel leaving this as-is. We've practically always regretted it when we made the parser discard information about what the user typed. For instance, I was just reminded yesterday that we really ought to distinguish SortClauses created due to user ORDER BY clauses from those created because the parser silently added 'em. How about simply documenting semantic equivalences, and making them somewhat more explicit to the user? Regards Haroldo -- Por favor registre [EMAIL PROTECTED] como mi nueva y unica direccion de correo en lugar de la vieja [EMAIL PROTECTED] Please, record [EMAIL PROTECTED] as my new and unique email address instead of ye old [EMAIL PROTECTED] Gracias. Thankyou. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transaction TODO list
Tom Lane wrote: I don't like using ParameterStatus because it's not designed for dealing with values that may change many times in a single query. Also it sends strings, which this really isn't. What about including the new nesting level in the SUBBEGIN/SUBCOMMIT/SUBABORT CommandStatus string? Yes, it's still a string for a numeric nesting level, but that's also how we pass numeric data such as insert/update rowcounts. I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. You could not directly tell the depth of your subxact from this, but do you need to? This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, so you could conceivably end up in situations where the driver thinks you're outside a transaction when you're actually inside a subtransaction, and issues spurious BEGINs. It's simple enough to handle the new states, but it *is* an incompatible protocol change. Please bump the protocol version if you do make this change. And if you're going to bump the protocol version, I have some other changes I'd like to see at the same time :) If named SAVEPOINT syntax (along the lines of what Oracle has) is available in 7.5, and there is a mechanism to COMMIT/ROLLBACK the top-level transaction directly, the JDBC driver doesn't actually need to know anything about subtransactions to correctly implement JDBC's commit, rollback, savepoint interface -- except perhaps to deal with the case where the application rolls back a subtransaction which invalidates an earlier SAVEPOINT. Having the nesting level available will probably make savepoint support a bit easier but it's not vital. If we don't have COMMIT/ROLLBACK of top-level transactions, then the JDBC driver needs to know the current nesting level so it knows how many subtransactions to deal with when a top-level commit/rollback is requested via the JDBC API. I'd actually prefer to see plain COMMIT and ROLLBACK continue to operate on the top-level transaction, since doing anything else is going to break pre-7.5 clients (such as older JDBC drivers) that assume that you're entirely outside a transaction after COMMIT/ROLLBACK. If we don't have SAVEPOINT syntax, then the JDBC driver needs to know the current nesting level so it can track which (client-maintained) savepoints are still valid in the face of the application doing subtransaction work itself. The only other thing the JDBC driver uses the transaction state for is to work out when BEGINs are needed when JDBC-level autocommit is off, and when to allow transaction isolation level changes. As far as I can see, in subtransaction is equivalent to in top-level transaction for both of those cases. So overall, knowing that you're in a subtransaction without knowing the nesting level does not seem very useful.. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
Oliver Jowett [EMAIL PROTECTED] writes: Tom Lane wrote: I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, Drat. Scratch that plan then. (Still, silently ignoring unrecognized states probably wasn't a good idea for the JDBC code...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transaction TODO list
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Tom Lane wrote: I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, Drat. Scratch that plan then. (Still, silently ignoring unrecognized states probably wasn't a good idea for the JDBC code...) True, but the alternative (screaming and yelling) would also have broken, just more obviously. Actually, thinking about it, that behaviour only changed recently, and from memory the older code completely ignored the transaction state in ReadyForQuery. The new driver probably hasn't spread too far yet. I'll sort out a patch so the driver breaks more obviously if it gets something unexpected. I still don't think that knowing you're in a subtransaction is very useful unless you also know the nesting level. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
On Sat, Jul 03, 2004 at 11:12:56PM -0400, Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Tom Lane wrote: I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, Drat. Scratch that plan then. (Still, silently ignoring unrecognized states probably wasn't a good idea for the JDBC code...) What about using the command tag of SUBBEGIN c ? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Hi! I'm a .signature virus! cp me into your .signature file to help me spread! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
