Re: [HACKERS] Named transaction
Hello. You wrote: TL Pavel Golub pa...@microolap.com writes: Is there any possibility that Postgres will have named transaction ever, like Firebird? TL What in heck is a named transaction, and why should we care? TL regards, tom lane Sorry guys, my bad. The thing is I'm not a Firebird adept either and this is my customer's maggot. Anyway, it's interesting, so I'll try to explain. 1. He has one connection 2. He has several scripts executed simultaneously (in different threads I think) 3. Each script must be executed inside it's own transaction In Firebird (Interbase) he may have several transactions per one connection each with it's own isolation level and parameters. Each transaction in Firebird has ID (or name?). That's why I called it named transaction. :) Moreover Firebird allows nested transactions (http://wiki.firebirdsql.org/wiki/index.php?page=IN+AUTONOMOUS+TRANSACTION) Now to implement customer's desire in PostgreSQL there are two ways: 1. Each script must be executed in the separate connection context 2. Each script must be executed inside critical section, in other words current scipt must block others until COMMIT or ROLLBACK I don't like both. How I imagine named transactions. :) START TRANSACTION first; INSERT ; -- inside first START TRANSACTION second; DELETE ; -- inside second SWITCH TRANSACTION first; -- switch context INSERT ; -- inside first COMMIT; -- first terminated, default context active (no transaction) SWITCH TRANSACTION second; -- activate second transaction context COMMIT; -- second terminated, no context active So, what do you think guys? -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Pavel Golub pa...@microolap.com writes: [ proposal involving ] SWITCH TRANSACTION first; -- switch context So, what do you think guys? No chance :-(. The amount of work that would be required is *vastly* out of proportion to any possible benefit. Use multiple connections. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On 06/18/2009 02:42 AM, Pavel Golub wrote: Now to implement customer's desire in PostgreSQL there are two ways: 1. Each script must be executed in the separate connection context 2. Each script must be executed inside critical section, in other words current scipt must block others until COMMIT or ROLLBACK I don't like both. What don't you like about 1) ? I don't know of any other databases that work this way. Using separate connections and connection pooling seems to be the way to go here. Personally, I found the named transaction concept a little skrewy unless: 1) SQL commands can be sent asynchronously as long as they are for different named transactions, even while other transactions are still running. 2) Each transaction runs in a different server-side thread. If this is what you want, it sounds like you are just trying to multiplex multiple queries and responses over the same TCP/IP connection. For the added complexity on both the client and the server, do you really think it is worth it? If you just want a connection multiplexor that is backed by a connection pool - I think that would be a lot easier to provide. :-) Cheers, mark -- Mark Mielkem...@mielke.cc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Named transaction
Hello, pgsql-hackers. Is there any possibility that Postgres will have named transaction ever, like Firebird? Now for each transaction client should open separate connection. But CONNECTION LIMIT option for database make this a little bit harder -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Pavel Golub pa...@microolap.com writes: Is there any possibility that Postgres will have named transaction ever, like Firebird? What in heck is a named transaction, and why should we care? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: Pavel Golub pa...@microolap.com writes: Is there any possibility that Postgres will have named transaction ever, like Firebird? What in heck is a named transaction, and why should we care? That Tom Lane, so warm and cuddly! David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
I'm curious what they ate too -- Greg On 17 Jun 2009, at 17:37, David E. Wheeler da...@kineticode.com wrote: On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: Pavel Golub pa...@microolap.com writes: Is there any possibility that Postgres will have named transaction ever, like Firebird? What in heck is a named transaction, and why should we care? That Tom Lane, so warm and cuddly! David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Greg Stark greg.st...@enterprisedb.com wrote: On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: Pavel Golub pa...@microolap.com writes: Is there any possibility that Postgres will have named transaction ever, like Firebird? What in heck is a named transaction, and why should we care? I'm curious what they ate too I don't know about Firebird, but in Sybase the transaction name just shows up in the process list, so you can tell what type of transaction is running. It's a solution to the what the heck is that idle transaction from -- as long as meaningful transaction names are consistently used. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On Wed, Jun 17, 2009 at 12:41 PM, Greg Starkgreg.st...@enterprisedb.com wrote: On 17 Jun 2009, at 17:37, David E. Wheeler da...@kineticode.com wrote: On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: Pavel Golub pa...@microolap.com writes: Is there any possibility that Postgres will have named transaction ever, like Firebird? What in heck is a named transaction, and why should we care? That Tom Lane, so warm and cuddly! I'm curious what they ate too Shrooms? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Tom Lane wrote: Pavel Golub pa...@microolap.com writes: Is there any possibility that Postgres will have named transaction ever, like Firebird? What in heck is a named transaction, and why should we care? Isn't this just another name for a subtransaction or inner transaction that can be separately committed? begin transaction bar; ... begin transaction foo; ... commit foo; ... rollback bar; foo's work is still committed. People have been hacking this up using dblink calls, I believe, but that's a horrid kludge. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Andrew Dunstan wrote: Tom Lane wrote: What in heck is a named transaction, and why should we care? Isn't this just another name for a subtransaction or inner transaction that can be separately committed? AFAIK that's an autonomous transaction, at least to some other RDBMSs. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Alvaro Herrera wrote: Andrew Dunstan wrote: Tom Lane wrote: What in heck is a named transaction, and why should we care? Isn't this just another name for a subtransaction or inner transaction that can be separately committed? AFAIK that's an autonomous transaction, at least to some other RDBMSs. Right, but since I had to ask what that was recently I though I'd use a bit more description :-) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Andrew Dunstan and...@dunslane.net writes: Alvaro Herrera wrote: AFAIK that's an autonomous transaction, at least to some other RDBMSs. Right, but since I had to ask what that was recently I though I'd use a bit more description :-) Yes, but some other followups suggest that maybe a named transaction does something else entirely. Thus my request for a definition of what the OP is actually asking for. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On Wed, Jun 17, 2009 at 6:40 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: Andrew Dunstan wrote: Tom Lane wrote: What in heck is a named transaction, and why should we care? Isn't this just another name for a subtransaction or inner transaction that can be separately committed? AFAIK that's an autonomous transaction, at least to some other RDBMSs. I have no idea what they are in Firebird but the name conjured up a different (interesting) idea for me. I had the image of naming a transaction and then being able to have other sessions join that same transaction. We've discussed this before for connection-pooled systems which want to be able to return their connection to the pool in the middle of their transaction. It would also possibly be useful for parallel data dumps and loads. -- Gregory Stark http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On Wed, Jun 17, 2009 at 3:04 PM, Greg Starkst...@enterprisedb.com wrote: On Wed, Jun 17, 2009 at 6:40 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: Andrew Dunstan wrote: Tom Lane wrote: What in heck is a named transaction, and why should we care? Isn't this just another name for a subtransaction or inner transaction that can be separately committed? AFAIK that's an autonomous transaction, at least to some other RDBMSs. I have no idea what they are in Firebird but the name conjured up a different (interesting) idea for me. I had the image of naming a transaction and then being able to have other sessions join that same transaction. We've discussed this before for connection-pooled systems which want to be able to return their connection to the pool in the middle of their transaction. It would also possibly be useful for parallel data dumps and loads. At the risk of veering off-topic, wouldn't this present some awfully nasty issues vis-a-vis the command counter? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Greg Stark wrote: On Wed, Jun 17, 2009 at 6:40 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: AFAIK that's an autonomous transaction, at least to some other RDBMSs. I have no idea what they are in Firebird but the name conjured up a different (interesting) idea for me. I had the image of naming a transaction and then being able to have other sessions join that same transaction. We've discussed this before for connection-pooled systems which want to be able to return their connection to the pool in the middle of their transaction. Sounds a bit like a prepared transaction, except that you don't put it aside for later commit but rather suspend it. It would also possibly be useful for parallel data dumps and loads. I think the clone snapshot stuff would be more easily usable for that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Tom Lane t...@sss.pgh.pa.us wrote: Yes, but some other followups suggest that maybe a named transaction does something else entirely. Thus my request for a definition of what the OP is actually asking for. Well, a quick google search suggests that all three guesses here were off base. This is the best clue I could find with a two-minute perusal: # TRANSACTION_HANDLE - use a named transaction. Firebird allows # multiple transactions per connection. In the case below, this # request is run in the system transaction - not available outside the # engine. The system transaction number is 0 and it is # pre-committed meaning that its changes are immediately visible to # all other transactions. Does that send a nasty chill up anyone else's spine? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On Wed, 2009-06-17 at 14:38 -0500, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Yes, but some other followups suggest that maybe a named transaction does something else entirely. Thus my request for a definition of what the OP is actually asking for. Well, a quick google search suggests that all three guesses here were off base. This is the best clue I could find with a two-minute perusal: # TRANSACTION_HANDLE - use a named transaction. Firebird allows # multiple transactions per connection. In the case below, this # request is run in the system transaction - not available outside the # engine. The system transaction number is 0 and it is # pre-committed meaning that its changes are immediately visible to # all other transactions. Does that send a nasty chill up anyone else's spine? That sounds like dirty read, IIRC. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Alvaro Herrera wrote: AFAIK that's an autonomous transaction, at least to some other RDBMSs. Right, but since I had to ask what that was recently I though I'd use a bit more description :-) Yes, but some other followups suggest that maybe a named transaction does something else entirely. Thus my request for a definition of what the OP is actually asking for. According to the (hard to find) Firebird docs (or rather, the old Interbase docs, which is all they have): A single application can start simultaneous transactions. InterBase extends transaction management and data manipulation statements to support transaction names, unique identifiers that specify which transaction controls a given statement among those transactions that are active. Transaction names must be used to distinguish one transaction from another in programs that use two or more transactions at a time. Each transaction started while other transactions are active requires a unique name and its own SET TRANSACTION statement. SET TRANSACTION can include optional parameters that modify a transaction’s behavior. There are four steps for using transaction names in a program: 1. Declare a unique host-language variable for each transaction name. In C and C++, transaction names should be declared as long pointers. 2. Initialize each transaction name to zero. 3. Use SET TRANSACTION to start each transaction using an available transaction name. 4. Include the transaction name in subsequent transaction management and data manipulation statements that should be controlled by a specified transaction. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Yes, but some other followups suggest that maybe a named transaction does something else entirely. Thus my request for a definition of what the OP is actually asking for. Well, a quick google search suggests that all three guesses here were off base. This is the best clue I could find with a two-minute perusal: # TRANSACTION_HANDLE - use a named transaction. Firebird allows # multiple transactions per connection. In the case below, this # request is run in the system transaction - not available outside the # engine. The system transaction number is 0 and it is # pre-committed meaning that its changes are immediately visible to # all other transactions. Does that send a nasty chill up anyone else's spine? Well, it does even more when you read in the docs that Firebase DDL can *only* take place in the context of the system transaction. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On Wed, Jun 17, 2009 at 8:09 PM, Robert Haasrobertmh...@gmail.com wrote: I have no idea what they are in Firebird but the name conjured up a different (interesting) idea for me. I had the image of naming a transaction and then being able to have other sessions join that same transaction. We've discussed this before for connection-pooled systems which want to be able to return their connection to the pool in the middle of their transaction. It would also possibly be useful for parallel data dumps and loads. At the risk of veering off-topic, wouldn't this present some awfully nasty issues vis-a-vis the command counter? I didn't say it would be easy :) I think the command counter might be ok (though I'm not sure we could pull off the same phantom cid trick we do now). But locking and all the per-transaction information stored in the pgproc info would be a problem. Basically anywhere in the code where we used session as a proxy for transaction... -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Andrew Dunstan and...@dunslane.net writes: According to the (hard to find) Firebird docs (or rather, the old Interbase docs, which is all they have): A single application can start simultaneous transactions. InterBase extends transaction management and data manipulation statements to support transaction names, unique identifiers that specify which transaction controls a given statement among those transactions that are active. Hmm. Okay, that squares with what the OP mentioned about being able to emulate it with multiple connections --- basically, he wants to service multiple concurrent transactions using just a single backend and client connection. I can't see us trying to support that ... if you think making the backend thread-safe is a daunting project, this is ten times worse. It would mean making *all* transaction-local storage anonymous instead of being able to use static variables. I suspect the serial nature of our FE/BE protocol would get in your way pretty darn quick, too, unless it's okay to not be able to switch to another one of the transactions while the one you just issued a command to remains busy. Just use multiple connections. That gets the job done today. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
