Обсуждение: 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:pavel@gf.microolap.com
Pavel Golub <pavel@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
On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: > Pavel Golub <pavel@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
I'm curious what they ate too -- Greg On 17 Jun 2009, at 17:37, "David E. Wheeler" <david@kineticode.com> wrote: > On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: > >> Pavel Golub <pavel@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
Greg Stark <greg.stark@enterprisedb.com> wrote: >> On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: >> >>> Pavel Golub <pavel@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
On Wed, Jun 17, 2009 at 12:41 PM, Greg Stark<greg.stark@enterprisedb.com> wrote: > On 17 Jun 2009, at 17:37, "David E. Wheeler" <david@kineticode.com> wrote: >> On Jun 17, 2009, at 8:08 AM, Tom Lane wrote: >>> Pavel Golub <pavel@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
Tom Lane wrote: > Pavel Golub <pavel@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
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 Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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
Andrew Dunstan <andrew@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
On Wed, Jun 17, 2009 at 6:40 PM, Alvaro Herrera<alvherre@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
On Wed, Jun 17, 2009 at 3:04 PM, Greg Stark<stark@enterprisedb.com> wrote: > On Wed, Jun 17, 2009 at 6:40 PM, Alvaro > Herrera<alvherre@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
Greg Stark wrote: > On Wed, Jun 17, 2009 at 6:40 PM, Alvaro > Herrera<alvherre@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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Tom Lane <tgl@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
On Wed, 2009-06-17 at 14:38 -0500, Kevin Grittner wrote: > Tom Lane <tgl@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: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Tom Lane wrote: > Andrew Dunstan <andrew@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 manipulationstatements 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 transactionsat a time. Each transaction started while other transactions are active requires a unique name and its ownSET 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 transactionname 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 controlledby a specified transaction. cheers andrew
Kevin Grittner wrote: > Tom Lane <tgl@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
On Wed, Jun 17, 2009 at 8:09 PM, Robert Haas<robertmhaas@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
Andrew Dunstan <andrew@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
Hello. You wrote: TL> Pavel Golub <pavel@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:pavel@gf.microolap.com
Pavel Golub <pavel@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
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 Mielke<mark@mielke.cc>