Named transaction

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:

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 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

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>