Re: Nested Transactions, Abort All

> >But 'BEGIN' in plpgsql does not start a [sub]transaction, it starts a
> >statement block. Are we intending to change that ? I think not.
> >
> >
> >
> There are two possibilities:
> Either BEGIN *does* start a subtransaction, or BEGIN does not. I don't
> see how two nesting level hierarchies in a function should be
> handleable, i.e. having independent levels of statements blocks and
> subtransactions.
>
> BEGIN [whatever] suggests that there's also a statement closing that
> block of [whatever], but it's very legal for subtransactions to have no
> explicit end; the top level COMMIT does it all.

An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could
mean start/end block and subtx. I do not really see a downside.
But, it would imho only make sense if the 'END SUB' would commit sub
or abort sub iff subtx is in aborted state (see my prev posting)

Andreas

>
> An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could
> mean start/end block and subtx. I do not really see a downside.
> But, it would imho only make sense if the 'END SUB' would commit sub
> or abort sub iff subtx is in aborted state (see my prev posting)
>
> Andreas
>
Hello,

is good idea use keywords "begin sub" and "end sub"? Programmers like me
will be an problems with reading and writing SP, because begin sub and
mostly end sub are keywords from visual basic with different sense.
BEGIN SUBTRANSACTION and END SUBTRANSACTION is longer but more readable

regards
Pavel Stehule

Pavel Stehule wrote:
> >
> > An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could
> > mean start/end block and subtx. I do not really see a downside.
> > But, it would imho only make sense if the 'END SUB' would commit sub
> > or abort sub iff subtx is in aborted state (see my prev posting)
> >
> > Andreas
> >
> Hello,
>
> is good idea use keywords "begin sub" and "end sub"? Programmers like me
> will be an problems with reading and writing SP, because begin sub and
> mostly end sub are keywords from visual basic with different sense.
> BEGIN SUBTRANSACTION and END SUBTRANSACTION is longer but more readable

I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
TRANSACTION and COMMIT NESTED TRANSACTION.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, 9 Jul 2004, Bruce Momjian wrote:

> I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
> TRANSACTION and COMMIT NESTED TRANSACTION.

Should I read this as pg will get its own implementation of sub
transactions and not implement the almost equivalent standard (sql99)
savepoint feature?

Will we in the future see savepoints as well? And when that happen, should
we then recommend that people use the standard feature and stay away from
the pg only feature?

Doesn't anyone but me think is all backwards?

--
/Dennis Björklund

Dennis Bjorklund wrote:

> On Fri, 9 Jul 2004, Bruce Momjian wrote:
>
>> I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
>> TRANSACTION and COMMIT NESTED TRANSACTION.
>
> Should I read this as pg will get its own implementation of sub
> transactions and not implement the almost equivalent standard (sql99)
> savepoint feature?
>
> Will we in the future see savepoints as well?

I'm not a core developer, but that is what it looks like.

> And when that happen, should
> we then recommend that people use the standard feature and stay away from
> the pg only feature?

Nested transactions and savepoints serve two different purposes. They have
some overlap, but for the most part solve two distinct problems.

>
> Doesn't anyone but me think is all backwards?
>

I don't think so, especially as there has been some talk of implimenting
savepoints as a subset of nested transactions.

--miker

On Fri, Jul 09, 2004 at 07:10:06PM +0200, Dennis Bjorklund wrote:
> On Fri, 9 Jul 2004, Bruce Momjian wrote:
>
> > I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
> > TRANSACTION and COMMIT NESTED TRANSACTION.
>
> Should I read this as pg will get its own implementation of sub
> transactions and not implement the almost equivalent standard (sql99)
> savepoint feature?

I think we should get both. Clearly savepoints do not allow for a
snapshot to be released; nested xacts do.

OTOH savepoints are trivial to implement once nested xacts are in place.
They are only syntactic sugar.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)

On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> Clearly savepoints do not allow for a snapshot to be released; nested
> xacts do.

Why not?

> OTOH savepoints are trivial to implement once nested xacts are in place.
> They are only syntactic sugar.

Not only, but simple yes. I'm just opposed to having the non standard
syntax added for the little gain they give over just having standard
savepoints.

--
/Dennis Björklund

On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> On Fri, 9 Jul 2004, Alvaro Herrera wrote:
>
> > Clearly savepoints do not allow for a snapshot to be released; nested
> > xacts do.
>
> Why not?

What is it?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)

Alvaro Herrera wrote:
> On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> >
> > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > xacts do.
> >
> > Why not?
>
> What is it?

Simon posted it. It is called RELEASE:

> BEGIN;
> SAVEPOINT x1;
> INSERT INTO ...;
> RELEASE SAVEPOINT x1;
> SAVEPOINT x1;
> INSERT INTO ...;
> RELEASE SAVEPOINT x1;
> SAVEPOINT x1;
> INSERT INTO ...;
> RELEASE SAVEPOINT x1;

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, Jul 09, 2004 at 03:34:47PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > >
> > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > xacts do.
> > >
> > > Why not?
> >
> > What is it?
>
> Simon posted it. It is called RELEASE:

We can't actually release anything (commit the subtransactions), because
they may be savepoints established after that point, and they are
logically "inside" the previously established ones. At RELEASE we can't
really release -- we just lose the name and thus the opportunity to
rollback to it.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Major Fambrough: You wish to see the frontier?
John Dunbar: Yes sir, before it's gone.

On Fri, 2004-07-09 at 20:34, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > >
> > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > xacts do.
> > >
> > > Why not?
> >
> > What is it?
>
> Simon posted it. It is called RELEASE:
>
> > BEGIN;
> > SAVEPOINT x1;
> > INSERT INTO ...;
> > RELEASE SAVEPOINT x1;
> > SAVEPOINT x1;
> > INSERT INTO ...;
> > RELEASE SAVEPOINT x1;
> > SAVEPOINT x1;
> > INSERT INTO ...;
> > RELEASE SAVEPOINT x1;

Yes, this is the DB2 and SQLAnywhere syntax.

Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
Oracle's support of autonomous transactions looks to be identical to
nested transactions (Alvaro's advice required there...). They don't
allow you to explicitly call them, but you can use BEGIN/COMMIT in a
host program that calls a stored procedure, which also contains
BEGIN/COMMIT, effectively giving nested txns.

(...hopefully clearing up any discussion on "intermediate commits"
whoever mentioned those...)

Best regards, Simon Riggs

Alvaro Herrera wrote:
> On Fri, Jul 09, 2004 at 03:34:47PM -0400, Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > > >
> > > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > > xacts do.
> > > >
> > > > Why not?
> > >
> > > What is it?
> >
> > Simon posted it. It is called RELEASE:
>
> We can't actually release anything (commit the subtransactions), because
> they may be savepoints established after that point, and they are
> logically "inside" the previously established ones. At RELEASE we can't
> really release -- we just lose the name and thus the opportunity to
> rollback to it.

Oh, good point. Yes, those savepoints in between are still active. But
do we release anything on subxact commit? I though it was only on xact
abort, and that does invalidate all the savepoints in between.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Simon Riggs wrote:
> On Fri, 2004-07-09 at 20:34, Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > > >
> > > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > > xacts do.
> > > >
> > > > Why not?
> > >
> > > What is it?
> >
> > Simon posted it. It is called RELEASE:
> >
> > > BEGIN;
> > > SAVEPOINT x1;
> > > INSERT INTO ...;
> > > RELEASE SAVEPOINT x1;
> > > SAVEPOINT x1;
> > > INSERT INTO ...;
> > > RELEASE SAVEPOINT x1;
> > > SAVEPOINT x1;
> > > INSERT INTO ...;
> > > RELEASE SAVEPOINT x1;
>
> Yes, this is the DB2 and SQLAnywhere syntax.
>
> Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.

I thouht ROLLBACK was different from RELEASE, no? I see ROLLBACK used
in SQL99 for savepoints:

ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
[ <savepoint clause> ]

RELEASE only discards the savepoint name, I thought.

> Oracle's support of autonomous transactions looks to be identical to
> nested transactions (Alvaro's advice required there...). They don't
> allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> host program that calls a stored procedure, which also contains
> BEGIN/COMMIT, effectively giving nested txns.

Oracle has nested transactions too? Can you supply an example?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, 2004-07-09 at 21:18, Bruce Momjian wrote:
> Simon Riggs wrote:
> >
> > Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
>
> I thouht ROLLBACK was different from RELEASE, no? I see ROLLBACK used
> in SQL99 for savepoints:
>
> ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
> [ <savepoint clause> ]
>
>
> RELEASE only discards the savepoint name, I thought.
>

ERR-OHH Yes, dead right. Forgive my confusion, I knew there was
something different about Oracle's support of RELEASE. Check out:

https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/ap_standard_sql001.htm

last thing on page...

> > Oracle's support of autonomous transactions looks to be identical to
> > nested transactions (Alvaro's advice required there...). They don't
> > allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> > host program that calls a stored procedure, which also contains
> > BEGIN/COMMIT, effectively giving nested txns.
>
> Oracle has nested transactions too? Can you supply an example?

It's hard to quote a short example.... so try this link instead

http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm

Wasn't there some description of autonomous transactions on the stuff I
sent previously?

Best Regards, Simon Riggs

On Fri, Jul 09, 2004 at 04:07:19PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:

> > We can't actually release anything (commit the subtransactions), because
> > they may be savepoints established after that point, and they are
> > logically "inside" the previously established ones. At RELEASE we can't
> > really release -- we just lose the name and thus the opportunity to
> > rollback to it.
>
> Oh, good point. Yes, those savepoints in between are still active. But
> do we release anything on subxact commit? I though it was only on xact
> abort, and that does invalidate all the savepoints in between.

Yes, we free some things. Granted it's not a lot, but we have stacks
for several things that will be always be growing with savepoints, but
we can chop their heads off with "commit nested."

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jason Tesser: You might not have understood me or I am not understanding you.
Paul Thomas: It feels like we're 2 people divided by a common language...

Simon Riggs wrote:
> On Fri, 2004-07-09 at 21:18, Bruce Momjian wrote:
> > Simon Riggs wrote:
> > >
> > > Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
> >
> > I thouht ROLLBACK was different from RELEASE, no? I see ROLLBACK used
> > in SQL99 for savepoints:
> >
> > ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
> > [ <savepoint clause> ]
> >
> >
> > RELEASE only discards the savepoint name, I thought.
> >
>
> ERR-OHH Yes, dead right. Forgive my confusion, I knew there was
> something different about Oracle's support of RELEASE. Check out:
>
> https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/ap_standard_sql001.htm
>
> last thing on page...

Interesting Oracle doesn't support RELEASE or savepoint levels:

T271, Savepoints Oracle supports this feature, except:

* Oracle does not support RELEASE SAVEPOINT.
* Oracle does not support savepoint levels.

> > > Oracle's support of autonomous transactions looks to be identical to
> > > nested transactions (Alvaro's advice required there...). They don't
> > > allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> > > host program that calls a stored procedure, which also contains
> > > BEGIN/COMMIT, effectively giving nested txns.
> >
> > Oracle has nested transactions too? Can you supply an example?
>
> It's hard to quote a short example.... so try this link instead
>
> http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm
>
> Wasn't there some description of autonomous transactions on the stuff I
> sent previously?

This is not a nested transaction. It is "autonomous" meaning it can
commit independent of the outer transaction:

The grand new benefit of autonomous transactions for database triggers
is that inside those triggers you can now issue COMMITs and ROLLBACKs,
statements that are otherwise not allowed in database triggers. The
changes you commit and roll back will not, however, affect the main
transaction that caused the database trigger to fire. They will only
apply to DML activity taking place inside the trigger itself (or through
stored program units called within the trigger).

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> > Simon posted it. It is called RELEASE:
>
> We can't actually release anything (commit the subtransactions), because
> they may be savepoints established after that point, and they are
> logically "inside" the previously established ones. At RELEASE we can't
> really release -- we just lose the name and thus the opportunity to
> rollback to it.

You can still perform the release. If we have

SAVEPOINT p1;

SAVEPOINT p2;

RELEASE p2;

then it's no problem, we released the topmost savepoint (commit the
corresponding subtransaction). And if we have

SAVEPOINT p1;

SAVEPOINT p2;

RELEASE p1;

now you are saying that we just forget the name p1 and the subtransaction
for p1 is still there for ever.

But one should also link the subtransaction for p1 to p2, so when p2 is
released then also the (now unnamed) subtransaction for p1 is commited. Of
course we can't release p1 as long as p2 is still active. p2 is logically
a subtransaction of p1.

One don't really need an explicit link. When p2 is released all one needs
to do is to look at the parent and see if that is still named, if not it
should be commited and so on until we reach a named one.

--
/Dennis Björklund

Dear all,

I've being following the discussion of the nested transaction. I
apologize for that I can't help asking my questions as I get more
confused about what exactly are nested transactions, at least as far as
the concurrency control goes.

It seems to me there are two different types of nested transactions,
both to improve the parallelism to a transaction, but they have
different semantics.

The first type of nested transactions, I believe as described in this paper:

http://portal.acm.org/citation.cfm?id=806709&dl=ACM&coll=portal

has the semantics that the inner (or children) transactions are totally
hidden within a outer (or parent) transaction. Concurrency control makes
sure not only the entire (including children) parent transaction is
serial with other (parent) transaction, but also all child transactions
are serial inside the parent transaction. Clearly, this speedup the
execution of the parent transaction when child transactions are executed
in parallel. I think this semantics is also documented here:

http://pybsddb.sourceforge.net/ref/transapp/nested.html

On the other hand, I believe another semantics of nested transactions is
that to allow child transactions to commit independently to the parent
transaction. The logger example in this link given by a previous post
in this discussion

http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm

is a good example on this semantics. As far as the concurrency control
goes, the parent transaction and the children transactions are treated
equally. I.e. if after a child transaction is finished and before its
parent transaction commits, a conflict with the child transaction will
not cause the parent transaction to rollback. Again, this allows more
parallelism to the the parent transaction.

Am I on the right track understanding the serializability semantics
here? I'd appreciate it if someone can direct me some authorative text
on these issues.

Thanks,

-Min

On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> Yes, we free some things. Granted it's not a lot, but we have stacks
> for several things that will be always be growing with savepoints,

They will not always be growing for savepoints, you can free things when
using savepoints just as with subtransactions.

--
/Dennis Björklund

Alvaro Herrera wrote:

> We can't actually release anything (commit the subtransactions), because
> they may be savepoints established after that point, and they are
> logically "inside" the previously established ones. At RELEASE we can't
> really release -- we just lose the name and thus the opportunity to
> rollback to it.

The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints
subsequent to the RELEASE:

1) Let S be the <savepoint name>.
2) If S does not identify a savepoint established in the current
savepoint level,then an exception condition is raised:savepoint
exception invalid specification.
3) The savepoint identified by S and all savepoints established in the
current savepoint level subsequent to the establishment of S are destroyed.

So it sounds like we can commit the subtransaction on RELEASE.

Note that this is *not* the same when a savepoint name is reused; that
just moves the name, but "contained" savepoints are still valid.

-O

Oliver Jowett wrote:

> The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints
> subsequent to the RELEASE:

"subsequent to the released savepoint" rather.

-O

Min Xu (Hsu) wrote:
> Dear all,
>
> I've being following the discussion of the nested transaction. I
> apologize for that I can't help asking my questions as I get more
> confused about what exactly are nested transactions, at least as far as
> the concurrency control goes.
>
> It seems to me there are two different types of nested transactions,
> both to improve the parallelism to a transaction, but they have
> different semantics.
>
> The first type of nested transactions, I believe as described in this paper:
>
> http://portal.acm.org/citation.cfm?id=806709&dl=ACM&coll=portal
>
> has the semantics that the inner (or children) transactions are totally
> hidden within a outer (or parent) transaction. Concurrency control makes
> sure not only the entire (including children) parent transaction is
> serial with other (parent) transaction, but also all child transactions
> are serial inside the parent transaction. Clearly, this speedup the
> execution of the parent transaction when child transactions are executed
> in parallel. I think this semantics is also documented here:
>
> http://pybsddb.sourceforge.net/ref/transapp/nested.html
>
> On the other hand, I believe another semantics of nested transactions is
> that to allow child transactions to commit independently to the parent
> transaction. The logger example in this link given by a previous post
> in this discussion
>
> http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm
>
> is a good example on this semantics. As far as the concurrency control
> goes, the parent transaction and the children transactions are treated
> equally. I.e. if after a child transaction is finished and before its
> parent transaction commits, a conflict with the child transaction will
> not cause the parent transaction to rollback. Again, this allows more
> parallelism to the the parent transaction.
>
> Am I on the right track understanding the serializability semantics
> here? I'd appreciate it if someone can direct me some authorative text
> on these issues.

You are actually talking about much more powerful nested transactions
than we have implemented currently. The first allows for parallel
execution, which is certainly interesting. The second allows
subtransactions to be committed/rolled back independent of the outer
transaction. We don't support that either.

Our current implementation merely allows parts of a transaction to be
rolled back using ROLLBACK NESTED.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote:
> On Fri, 9 Jul 2004, Alvaro Herrera wrote:
>
> > Yes, we free some things. Granted it's not a lot, but we have stacks
> > for several things that will be always be growing with savepoints,
>
> They will not always be growing for savepoints, you can free things when
> using savepoints just as with subtransactions.

I still don't see when I can release a savepoint's state.

You showed a particular case, where we can finish a released savepoint
that is the innermost transaction. However, as soon as there is another
savepoint set after the released savepoint was set, we can't free the
second.

I mean this:

begin;
... work ...;
savepoint foo;
... more work ...;
savepoint bar;
... yet more ... ;
release foo;

At this time I can't release savepoint foo because the implementation
(nested) requires me to keep it open as long as savepoint bar exists.
If I released bar at a later time, I could close both, but not before.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"¿Qué importan los años? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)

On Sat, Jul 10, 2004 at 09:46:56AM +1200, Oliver Jowett wrote:
> Alvaro Herrera wrote:
>
> >We can't actually release anything (commit the subtransactions), because
> >they may be savepoints established after that point, and they are
> >logically "inside" the previously established ones. At RELEASE we can't
> >really release -- we just lose the name and thus the opportunity to
> >rollback to it.
>
> The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints
> subsequent to the RELEASE:

In our case, invalidating a savepoint does not mean we can release its
resources. We can only do that if it's the latest defined savepoint.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cuando miro a alguien, más me atrae cómo cambia que quién es" (J. Binoche)

On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> I mean this:
>
> begin;
> ... work ...;
> savepoint foo;
> ... more work ...;
> savepoint bar;
> ... yet more ... ;
> release foo;
>
>
> At this time I can't release savepoint foo because the implementation
> (nested) requires me to keep it open as long as savepoint bar exists.
> If I released bar at a later time, I could close both, but not before.

Yes, and that is exactly what should be done, what is wrong with that
behaviour?

If you do the same as above with nested transactions

BEGIN;
... work ...;
SUBBEGIN;
... more work ...;
SUBBEGIN;
... yet more ... ;

and now you can only commit the last subbegin. Subtransactions does not
give you anything more then savepoints in this example.

If anything there might be a possibility to do more with savepoints then
nested transactions since as you say, you can release an earlier savepoint
then the last. But that is something one can try to optimize later, if
possible to optimize at all.

Subtransactions can _not_ free more things then savepoints can. It's just
an empty argument.

In fact, I still see no real advantage to subtransactions at all. We are
only playing the lock-in game when we introduce postgresql features that
do almost the same thing as standard features.

--
/Dennis Björklund

On Fri, 9 Jul 2004, Mike Rylander wrote:

> Nested transactions and savepoints serve two different purposes. They have
> some overlap, but for the most part solve two distinct problems.

Then show some examples that illustrait the difference. So far all
examples shown that uses subtransactions could just as well have been
written using savepoints.

I don't agree that they have two different purposes.

> I don't think so, especially as there has been some talk of implimenting
> savepoints as a subset of nested transactions.

It is not a subset. It's the other way around. Nested transactions are a
subset of savepoints

Savepoints have more possibilities, you can invalidate older savepoints
then the last (with subtransactions you can only commit/rollback the
last). If you don't use that then it's exactly the same as
subtransactions.

The only "feature" subtransactions have that savepoints doesn't is the
lack of names. Every savepoint have a name. If we want an extension it
could be to get the database to generate a fresh savepoint name. The
client can of course also generate unique savepoint names if it want.

That subtransactions do more than savepoints is just smoke an mirrors. So
far there have been no example to validate that point of view, and I don't
think there will be any. If anyone know of something that you can do with
subtransactions and not with savepoints, please speak up.

--
/Dennis Björklund

On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote:
> > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> >
> > > Yes, we free some things. Granted it's not a lot, but we have stacks
> > > for several things that will be always be growing with savepoints,
> >
> > They will not always be growing for savepoints, you can free things when
> > using savepoints just as with subtransactions.
>
> I still don't see when I can release a savepoint's state.
>
> You showed a particular case, where we can finish a released savepoint
> that is the innermost transaction. However, as soon as there is another
> savepoint set after the released savepoint was set, we can't free the
> second.
>
> I mean this:
>
> begin;
> ... work ...;
> savepoint foo;
> ... more work ...;
> savepoint bar;
> ... yet more ... ;
> release foo;
>
>
> At this time I can't release savepoint foo because the implementation
> (nested) requires me to keep it open as long as savepoint bar exists.
> If I released bar at a later time, I could close both, but not before.

According to ANSI 2003, savepoints should be considered in terms of
nesting. That is, the spec talks to nesting levels (4.35.2):

"An SQL-transaction has one or more savepoint levels, exactly one of which
is the current savepoint level. The savepoint levels of an SQL-transaction
are nested, such that when a new savepoint level NSL is established, the
current savepoint level CSL ceases to be current and NSL becomes current.
When NSL is destroyed, CSL becomes current again."

And:

"If a <rollback statement> references a savepoint SS, then all changes
made to SQL-data or schema subsequent to the establishment of the
savepoint are canceled, all savepoints established since SS was
established are destroyed, and the SQL-transaction is restored to its
state as it was immediately following the execution of the <savepoint
statement>."

This is also relevant:

"It is implementation-defined whether or not, or how, a <rollback
statement> that references a <savepoint specifier> affects diagnostics
area contents, the contents of SQL descriptor areas, and the status of
prepared statements."

So, releasing foo would release bar (16.5):

"3) The savepoint identified by S and all savepoints established in the
current savepoint level subsequent to the establishment of S are
destroyed."

Also, the spec makes mention of savepoint behaviour in functions (10.4):

"2) If, before the completion of the execution of the SQL routine body of
R, an attempt is made to execute an SQL-transaction statement that is not
a <savepoint statement> or a <release savepoint statement>, or is a <rollback
statement> that does not specify a <savepoint clause>, then an exception
condition is raised: SQL routine exception prohibited SQL-statement
attempted. "

It also states that an SQL-invoked function lives in its own savepoint
level (4.27):

"An SQL-invoked procedure may optionally be specified to require a new
savepoint level to be established when it is invoked and destroyed on
return from the executed routine body. The alternative of not taking a
savepoint can also be directly specified with OLD SAVEPOINT LEVEL. When an
SQL-invoked function is invoked a new savepoint level is always
established."

We do not currently support SQL-invoked procedures (that is, routines
executed from SQL with CALL <procname>, which don't need to return a value
and which can accept IN OUT and OUT parameters) so we need only deal with
the SQL-invoked function case.

So, running back to 10.4:

"12) If R is an SQL-invoked function or if R is an SQL-invoked procedure
and the descriptor of R includes an indication that a new savepoint level
is to be established when R is invoked, then the current savepoint level
is destroyed."

So, any savepoints created during the function are destroyed.

What isn't clearly discussed is what they mean by destroy. That is, the
1) ability to reference the savepoint, or 2) all modifications to SQL-data
made since the savepoint was created. I cannot see how it could be (2) can
be the case.

Section 16.5 discusses <release savepoint statement> whose function is to
'destroy a savepoint':

"3) The savepoint identified by S and all savepoints established in the
current savepoint level subsequent to the establishment of S are
destroyed."

It makes no reference to have any effect like rollback.

So, I think that we can only release things once we rollback to a
savepoint or once we commit.

This is not to say we should follow this implementation. I've dug this up
to try and present one (reasonably) consistent perspective on it.

Thanks,

Gavin

On Sat, 10 Jul 2004, Gavin Sherry wrote:

> "3) The savepoint identified by S and all savepoints established in the
> current savepoint level subsequent to the establishment of S are
> destroyed."

So the standard savepoints are even more like the subtransactions that
alvaro have implemented then I realised before.

One can not just release an earlier savepoint and keep a later one.
Interesting.

--
/Dennis Björklund

Bruce Momjian wrote:

>
>Interesting Oracle doesn't support RELEASE or savepoint levels:
>
>T271, Savepoints Oracle supports this feature, except:
>
> * Oracle does not support RELEASE SAVEPOINT.
>
>
Yes, and IMHO it's just some housekeeping stuff, informing the backend
that you'll never want to rollback to that savepoint any more. If there
are no resources to release internally, a noop.

> * Oracle does not support savepoint levels.
>
>
The levels are created implicitely, because a rollback to savepoint1
will rollback *all* work done since then. This effectively makes all
subsequent savepoints children of the first. The effect of RELEASE
SAVEPOINT would be to restrict the tree depth, by concentrating all
released savepoints into their parents.

>
>This is not a nested transaction. It is "autonomous" meaning it can
>commit independent of the outer transaction:
>

I like that too... in 7.6.

Regards,
Andreas

Alvaro Herrera wrote:
> On Sat, Jul 10, 2004 at 09:46:56AM +1200, Oliver Jowett wrote:
>
>>Alvaro Herrera wrote:
>>
>>
>>>We can't actually release anything (commit the subtransactions), because
>>>they may be savepoints established after that point, and they are
>>>logically "inside" the previously established ones. At RELEASE we can't
>>>really release -- we just lose the name and thus the opportunity to
>>>rollback to it.
>>
>>The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints
>>subsequent to the RELEASE:

(that should read "subsequent to the released savepoint")

> In our case, invalidating a savepoint does not mean we can release its
> resources. We can only do that if it's the latest defined savepoint.

I don't understand why this is true if the invalidation comes from a
RELEASE statement. I understand the problems with savepoint name reuse
invalidating an earlier savepoint -- we do have to keep the earlier txn
open in that case.

Say I have:

SAVEPOINT s1
-- work 1
SAVEPOINT s2
-- work 2
RELEASE SAVEPOINT s1 -- Invalidates s1 and s2

Can't we translate that to:

begin subtransaction s1
-- work 1
begin subtransaction s2
-- work 2
commit subtransaction s1 -- and implicitly s2

We don't need to keep subtransaction s2 open -- we will never need to
roll it back as the RELEASE of s1 invalidates it.

What am I missing?

-O

<posted & mailed>

Dennsnippetssklund wrote:

> On Fri, 9 Jul 2004, Mike Rylander wrote:
>
>> Nested transactions and savepoints serve two different purposes. They
>> have some overlap, but for the most part solve two distinct problems.
>
> Then show some examples that illustrait the difference. So far all
> examples shown that uses subtransactions could just as well have been
> written using savepoints.
>

After seeing some more snippets of the SQL2003 spec it seems that this is
true, and that there is more of a syntactic difference than functional.
This does not seem to be the case for Oracle (the other major
implementation that has been cited for SAVEPOINT syntax), as savepoints in
Oracle are not logically nested. Note that I am going on the statements
from others on this list for this point...

> I don't agree that they have two different purposes.

They do, if only to make particular constructs easier to write. This is an
opinion, but for example an EXCEPTION framework for plpgsql would be easier
to implement and use if it used the nested transactions rather than
savepoint syntax:

CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
BEGIN
BEGIN NESTED;
do some work...
BEGIN NESTED;
do other work...
EXCEPTION WHEN SQLSTATE = already_exists THEN
do alternate work with its own error checking...
END NESTED;
EXCEPTION WHEN SQLSTATE = fkey_violation THEN
ROLLBACK NESTED;
END NESTED;
END;';

I realize this can be done with nested savepoints and that is what the spec
requires, but in other major implementations of savepoints this nested
exception handling would be more difficult to write. Again, simply my
opinion.

>
>> I don't think so, especially as there has been some talk of implementing
>> savepoints as a subset of nested transactions.
>
> It is not a subset. It's the other way around. Nested transactions are a
> subset of savepoints

Perhaps I got my wires crossed a bit there. And again, after looking at
some more of the SQL2003 spec this does seem to be the case. I cry your
pardon! :)

>
> Savepoints have more possibilities, you can invalidate older savepoints
> then the last (with subtransactions you can only commit/rollback the
> last).

This implies that savepoints are flat. It won't be that way under the
covers, but it does give that impression, and flat savepoint space is
definitely suited to a different class of problems than nested
transactions.

> If you don't use that then it's exactly the same as
> subtransactions.
>

I don't see this. Nested transactions present a hierarchal interface to the
user, savepoints don't, especially considering that those familiar with
PL/SQL know that savepoints are not nested. Now, savepoints can be used IN
a hierarchy, but they do not DEFINE one as nested transactions do.

I look at it this way: Let's have both, and where a user wants a flat
transaction space, as that may suit the needs of the problem, they will use
SAVEPOINT syntax; if the user would perfer an explicit hierarchy they can
use nested transactions. Everyone wins!

> The only "feature" subtransactions have that savepoints doesn't is the
> lack of names. Every savepoint have a name. If we want an extension it
> could be to get the database to generate a fresh savepoint name. The
> client can of course also generate unique savepoint names if it want.

I don't think they can be compared like that, feature for feature. Although
I agree with you that they provide extremely similar feature sets, the
present different interfaces to the user. They may end up being backed by
the exact same code but the syntax and logical structure will surely
differ, and when a user wants labeled rollback point they will use
savepoints. When s/he wants hierarchical rollback points they will use the
nested transactions syntax.

BTW, I would imagine that savepoints will be implemented as nested
transactions with detachable labels... the label can move from a
transaction to one of its descendants, and that outer (sub)transaction will
be implicitly COMMITed with its parent.

>
> That subtransactions do more than savepoints is just smoke an mirrors. So
> far there have been no example to validate that point of view, and I don't
> think there will be any. If anyone know of something that you can do with
> subtransactions and not with savepoints, please speak up.
>

You have opened my eyes to the fact that savepoints and nested transactions
can be used for most of the same problems, however I don't see this as a
one-or-the-other proposition.

Alvaro found it easier to implement nested transactions, he forged ahead and
did it. Now, because of good design or simple luck, we should be able to
implement savepoints fairly easily. To me this is the best we could have
hoped for, as it means that not only will be support the entire SQL2003
spec WRT savepoints, we actually get to present a richer interface to the
user, one that includes a feature explicitly designed to model the
hierarchical nature of certain datasets and/or solutions. Of course anyone
reading this can guess which interface I am looking forward to, but the
point is that we will have both where most others don't have a complete
implementation of either!

--miker

On Sat, 10 Jul 2004, Mike Rylander wrote:

> They do, if only to make particular constructs easier to write. This is an
> opinion, but for example an EXCEPTION framework for plpgsql would be easier
> to implement and use if it used the nested transactions rather than
> savepoint syntax:
>
> CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
> BEGIN
> BEGIN NESTED;
> do some work...
> BEGIN NESTED;
> do other work...
> EXCEPTION WHEN SQLSTATE = already_exists THEN
> do alternate work with its own error checking...
> END NESTED;
> EXCEPTION WHEN SQLSTATE = fkey_violation THEN
> ROLLBACK NESTED;
> END NESTED;
> END;';
>
> I realize this can be done with nested savepoints and that is what the spec
> requires,

Lets look at what it can look like:

BEGIN
SAVEPOINT nested;
do some work...
SAVEPOINT nested2;
do other work...
EXCEPTION WHEN SQLSTATE = already_exists THEN
ROLLBACK TO SAVEPOINT nested2;
do alternate work with its own error checking...
RELEASE nested2;
EXCEPTION WHEN SQLSTATE = fkey_violation THEN
ROLLBACK TO SAVEPOINT nested;
RELEASE nested;
END;

Now, in what way is this more complicated?

I'm not 100% sure how the exceptions that you used above work. Do that
always rollback the transaction thay are in? In one of the exceptions you
did a rollback but not in the other. In my example I added a rollback in
the first exception handler. Maybe you forgot it there?

In any case. I don't see this as any harder then your example.

> > Savepoints have more possibilities, you can invalidate older savepoints
> > then the last (with subtransactions you can only commit/rollback the
> > last).
>
> This implies that savepoints are flat. It won't be that way under the
> covers, but it does give that impression, and flat savepoint space is
> definitely suited to a different class of problems than nested
> transactions.

First, my claim above was wrong. As Gavin pointed out in another mail, if
one have savepoints p1 and p2 and release p1 then also p2 is released.
It's possible to implement both kinds of behaviour using Alvaros work, but
the standard demands the simpler one where p2 is also released.

Now, about the flatness. Savepoints are not flat. They are sort of flat in
a savepoint level. But, for example when you call a function you get a new
savepoint level. I actually don't want to call it flat at all. The example
above does not overwrite the savepoints "nested" and "nested2" that might
exist before the call, since this is a higher savepoint level.

I'm not sure exactly what it is that defines a new savepoint level, but a
function call does and maybe some other things.

> BTW, I would imagine that savepoints will be implemented as nested
> transactions with detachable labels... the label can move from a
> transaction to one of its descendants, and that outer (sub)transaction will
> be implicitly COMMITed with its parent.

Yes. That's my view as well.

> Alvaro found it easier to implement nested transactions, he forged ahead and
> did it. Now, because of good design or simple luck, we should be able to
> implement savepoints fairly easily.

I think the difference between them are so small that it's not a big deal
at all. In my view savepoints and nested transactions are almost the same
thing. The only difference being that the savepoints have names.
Savepoints are nested. You can not have savepoints p1 and then p2 and try
to only rollback p1. Then you rollback p2 as well, why. Because they are
nested.

> spec WRT savepoints, we actually get to present a richer interface to the
> user

If it's richer or not is the question. And then one have to compare that
to the downside of adding a non standard interface.

I don't think it is richer at all, but I'd be happy to change my mind if
someone can show an example where nested transactions solve something that
you can't just as well solve with savepoints.

--
/Dennis Björklund

Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> writes:
> On Fri, 9 Jul 2004, Mike Rylander wrote:
>> Nested transactions and savepoints serve two different purposes. They have
>> some overlap, but for the most part solve two distinct problems.

> Then show some examples that illustrait the difference. So far all
> examples shown that uses subtransactions could just as well have been
> written using savepoints.

And vice versa. It's a matter of convenience of notation, and I tend
to agree with Mike's comment that each wins in some cases.

> Savepoints have more possibilities, you can invalidate older savepoints
> then the last

Nonsense. Invalidating an older savepoint must invalidate everything
after it as well. The fact that the savepoint syntax allows you to
express conceptually-ridiculous operations (like that one) is not a
point in its favor IMHO.

regards, tom lane

On Sat, 10 Jul 2004, Tom Lane wrote:

> Nonsense. Invalidating an older savepoint must invalidate everything
> after it as well. The fact that the savepoint syntax allows you to
> express conceptually-ridiculous operations (like that one) is not a
> point in its favor IMHO.

Luckily the standard was written like that!

On the other hand, it's not hard to implement the other behaviour either
if that is what one wants (and we don't). It would only forget the name of
the earlier savepoint. The corresponding transaction in itself have to
stay.

--
/Dennis Björklund

<posted & mailed>

Dennis Bjorklund wrote:

> On Sat, 10 Jul 2004, Mike Rylander wrote:
>
>> They do, if only to make particular constructs easier to write. This is
>> an opinion, but for example an EXCEPTION framework for plpgsql would be
>> easier to implement and use if it used the nested transactions rather
>> than savepoint syntax:
>>
>> CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
>> BEGIN
>> BEGIN NESTED;
>> do some work...
>> BEGIN NESTED;
>> do other work...
>> EXCEPTION WHEN SQLSTATE = already_exists THEN
>> do alternate work with its own error checking...
>> END NESTED;
>> EXCEPTION WHEN SQLSTATE = fkey_violation THEN
>> ROLLBACK NESTED;
>> END NESTED;
>> END;';
>>
>> I realize this can be done with nested savepoints and that is what the
>> spec requires,
>
> Lets look at what it can look like:
>
> BEGIN
> SAVEPOINT nested;
> do some work...
> SAVEPOINT nested2;
> do other work...
> EXCEPTION WHEN SQLSTATE = already_exists THEN
> ROLLBACK TO SAVEPOINT nested2;
> do alternate work with its own error checking...
> RELEASE nested2;
> EXCEPTION WHEN SQLSTATE = fkey_violation THEN
> ROLLBACK TO SAVEPOINT nested;
> RELEASE nested;
> END;
>
>
> Now, in what way is this more complicated?

Only in that you need to define a name for each savepoint in order to create
the hierarchy. And that is my point, savepoints impose more work on the
user to create a logical hierarchy, not that they cannot be used for
hierarchical structures.

>
> I'm not 100% sure how the exceptions that you used above work. Do that
> always rollback the transaction thay are in? In one of the exceptions you
> did a rollback but not in the other. In my example I added a rollback in
> the first exception handler. Maybe you forgot it there?

That was just pseudo-code and wholly invented in my head, but based on an
earlier expample of possible EXCEPTION syntax. The idea is that when a
subtransaction is in an aborted state due to an error the EXCEPTION clause
would implicitly roll back that subtransaction and open a new transaction
from its own block. This EXCEPTION subtrans is only used in the case of an
error in the matching BEGIN NESTED block, and the two share the COMMIT
statement, syntacticly speaking. Think of it as a "try { ... } catch
[type] { ... } finally { commit }" type structure.

>
> In any case. I don't see this as any harder then your example.
>

It's not harder, per se, but it does impose a more difficult to maintain
syntax, IMHO.

>> > Savepoints have more possibilities, you can invalidate older savepoints
>> > then the last (with subtransactions you can only commit/rollback the
>> > last).
>>
>> This implies that savepoints are flat. It won't be that way under the
>> covers, but it does give that impression, and flat savepoint space is
>> definitely suited to a different class of problems than nested
>> transactions.
>
> First, my claim above was wrong. As Gavin pointed out in another mail, if
> one have savepoints p1 and p2 and release p1 then also p2 is released.
> It's possible to implement both kinds of behaviour using Alvaros work, but
> the standard demands the simpler one where p2 is also released.
>
> Now, about the flatness. Savepoints are not flat. They are sort of flat in
> a savepoint level. But, for example when you call a function you get a new
> savepoint level. I actually don't want to call it flat at all. The example
> above does not overwrite the savepoints "nested" and "nested2" that might
> exist before the call, since this is a higher savepoint level.
>

OK, savepoints are not REALLY flat, but they are not hierarchically nested
either. They are cumulative. They can be used, as you showed above, in a
hierarchy, but as I said, they are not by their nature "nested".

> I'm not sure exactly what it is that defines a new savepoint level, but a
> function call does and maybe some other things.
>

As for savepoint levels in functions, that is a scoping issue imposed by the
functions themselves, not by the savepoint syntax. It would be nonsensical
to rollback to a savepoint outside a function, just as it would be
nonsensical to rollback the outer transaction from within the function.
Allowing either would cause undesired "action at a distance" and possibly
violate the A in ACID. The way I see it, savepoint levels should be
specified by function calls, as you said, and by the transaction nesting
level.

>> BTW, I would imagine that savepoints will be implemented as nested
>> transactions with detachable labels... the label can move from a
>> transaction to one of its descendants, and that outer (sub)transaction
>> will be implicitly COMMITed with its parent.
>
> Yes. That's my view as well.
>

Well, at least we agree on that ;)

>> Alvaro found it easier to implement nested transactions, he forged ahead
>> and
>> did it. Now, because of good design or simple luck, we should be able to
>> implement savepoints fairly easily.
>
> I think the difference between them are so small that it's not a big deal
> at all. In my view savepoints and nested transactions are almost the same
> thing. The only difference being that the savepoints have names.
> Savepoints are nested. You can not have savepoints p1 and then p2 and try
> to only rollback p1. Then you rollback p2 as well, why. Because they are
> nested.
>

Well, at this point there is a great difference when compared to other
implementations. And, in reality, that is our competition. The spec is
there to "level the playing field", as it were. And with a nested
transaction-backed implementation of savepoints we will be closer to the
goal line than our competition.

>> spec WRT savepoints, we actually get to present a richer interface to the
>> user
>
> If it's richer or not is the question. And then one have to compare that
> to the downside of adding a non standard interface.
>

And the upside, which I consider great. I could see it becoming an
implementation leader for others to follow.

> I don't think it is richer at all, but I'd be happy to change my mind if
> someone can show an example where nested transactions solve something that
> you can't just as well solve with savepoints.
>

It is yet to be seen if nested transactions in PG will be everythink I hope
they can be, and perhaps the benefits will indeed be individually
qualitative, instead of globally quantitative.

Therefore, I don't know if I can show empirically that having user-exposed
nested transaction is "better" because it comes down to individual choice
of style. I can definitely see nested transactions containing (and
defining the level of) savepoints as being a HUGE boon to the logical
maintainability of stored procedures and long running, recurring scripts.

The end result will be more tools in the hands of users. I am sure the docs
will explain that (currently) PG nested transactions are an extention to
the standard.

--
--miker

On Sat, Jul 10, 2004 at 06:22:00PM +0200, Dennis Bjorklund wrote:

> On the other hand, it's not hard to implement the other behaviour either
> if that is what one wants (and we don't). It would only forget the name of
> the earlier savepoint. The corresponding transaction in itself have to
> stay.

That's why it's absurd. Why allow an operation which isn't really an
operation?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"God is real, unless declared as int"

People,

Are we perhaps getting away from the issues here? The reason for this
discussion was to determine the user-level syntax for Alvaro's nested
transactions. We can discuss all we want about how he should have maybe
implemented some things differently, but we're supposed to start beta-testing
in 5 days and the current tangentalism of this discussion is unlikely to
produce such a result.

What we really have to determine is one of 4 options regarding the syntax for
Alvaro's patch:

1) We adopt one of the two PostgreSQL-specific syntaxes suggested by Alvaro,
based on SUBBEGIN or BEGIN NESTED. This would probably be the easiest
solution, but adds inconsistency with both the standard and other databases.

2) We adopt the syntax of the other databases to really push Nested
Transactions (as opposed to Savepoints), namely MSSQL and SyBase. This would
aid thousands of DBAs wishing to migrate to PostgreSQL, but would also mean
adopting a logically inconsistent syntax which is even further from the
standard than Alvaro's proposal.

3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax. This would
have the twin benefit of both allowing us to improve our standards compliance
and make savepoints completely compliant in the next version, as well as
helping those wishing to migrate from Oracle to PostgreSQL (currently our
largest source of migrations). Its disadvantage is the subtle differences
between Alvaro's patch and the standard, which might not be obvious to users
and lead to difficult to locate errors. This option also comes in two
flavors:
a) we implement savepoint names, troubleshooting the namespace and scoping
issues, which would really make this a different feature and delay beta
testing, or
b) we do anonymous savepoints for now, which more-or-less exactly matches the
current behavior of Alvaro's patch, and do complaint, named savepoints in the
next version.

4) We hold back this patch until the next version. There is some merit in
this, due to the lack of consensus on functionality and Alvaro's
dissapointing discovery that we will not be able to use savepoints in
functions until next version. However, it would also mean effectively
dropping a major feature from 7.5 pretty much because we can't make up our
minds, and because nobody gave Alvaro adequate feedback when it was more
timely.

If you couldn't tell, I favor option 3) b) This syntax would look like:

BEGIN TRANSACTION; --begin main
do stuff;
SAVEPOINT; -- begin "nested transaction 1"
do more stuff;
SAVEPOINT; -- begin "nested transaction 2" inside "NT 1"
do stuff;
RELEASE SAVEPOINT; -- "commit" NT 2
do some more stuff;
test conditions: if bad:
ROLLBACK TO SAVEPOINT; -- rollback NT1, erasing NT2 in the process
if good:
RELEASE SAVEPOINT; -- "commit" NT1 and by implication NT2
do some more stuff
tests: if problem:
ROLLBACK; -- rollback entire transaction, including NT1 and NT2;
if good:
COMMIT; -- commit entire transaction, including NT1 and/or NT2
if they were good, excluding them if they were rolled back

In other words:
SAVEPOINT == BEGIN NESTED
RELEASE SAVEPOINT == COMMIT NESTED
ROLLBACK TO SAVEPOINT == ROLLBACK NESTED

If I'm not mistaken, the above matches the functionality already coded by
Alvaro. It begins but does not complete our compliance with SQL3 Savepoint
syntax, putting us on the right road but making developers aware that there
are some differences between our implementation and the standard. Thus
developers would be able to adopt the current syntax now, and the same
applications would still run when we complete standards-compliant syntax
later.

HOWEVER, I do still find one major flaw in Alvaro's implementation that I
can't seem to get other people on this list to take seriously, or maybe I'm
just not understanding the answers. One-half the point of Savepoints/Nested
Transactions is the ability to recover from certain kinds of errors (like
duplicate keys) inside a transaction and still commit the transaction after
the abort condition has been rolled back.
But the ability to detect an abort state *from the SQL command line* (or a
database port connection) has not been addressed. I've seen some comments
about functions to find an abort state from libpq in the text, but I'm not
even clear if this has been coded or is just theoretical. Parsing the
output of STDERR is *not* adequate. We need to be able to query whether we
are in an abort state, or we make NTs absolutely useless to any client
application that has connections which cannot, or do not yet, incorporate new
libpq functions, something which could take considerable time after the 7.5
release.
Do we already have an ability to query the SQLSTATE from the command line?
If so, what numbers indicate an abort state, if any?
Without this issue being addressed, I will change my opinion and vote for
option (4) because clearly the NT patch will not be ready for prime-time.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Sat, 10 Jul 2004, Alvaro Herrera wrote:

> That's why it's absurd. Why allow an operation which isn't really an
> operation?

Same reason why you allow an addition with 0. One can say that it's
not really an operation either.

One can have many different semantics, here are 3 versions:

1) You release savepoints in any order
2) You release savepoints in reverse order
3) You release any savepoints and later ones then the
one you released are automatically released.

I don't see any of these as absurd. The ansi spec uses number 3.

It might seem absurd to you, given the implementation you have made.

--
/Dennis Björklund

On Sat, Jul 10, 2004 at 09:46:00PM +0200, Dennis Bjorklund wrote:

> One can have many different semantics, here are 3 versions:
>
> 1) You release savepoints in any order
> 2) You release savepoints in reverse order
> 3) You release any savepoints and later ones then the
> one you released are automatically released.
>
> I don't see any of these as absurd. The ansi spec uses number 3.

Ah-ha, now I see what I failed to see previously: all later savepoints
are also released! This makes a lot more sense. So "release" is
exactly like commit nested, allowing several levels to be "committed".

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)

Josh Berkus wrote:
> If you couldn't tell, I favor option 3) b) This syntax would look like:
>
> BEGIN TRANSACTION; --begin main
> do stuff;
> SAVEPOINT; -- begin "nested transaction 1"
> do more stuff;
> SAVEPOINT; -- begin "nested transaction 2" inside "NT 1"
> do stuff;
> RELEASE SAVEPOINT; -- "commit" NT 2
> do some more stuff;
> test conditions: if bad:
> ROLLBACK TO SAVEPOINT; -- rollback NT1, erasing NT2 in the process
> if good:
> RELEASE SAVEPOINT; -- "commit" NT1 and by implication NT2
> do some more stuff
> tests: if problem:
> ROLLBACK; -- rollback entire transaction, including NT1 and NT2;
> if good:
> COMMIT; -- commit entire transaction, including NT1 and/or NT2
> if they were good, excluding them if they were rolled back

Well, Oracle doesn't suppor RELEASE, so we are matching the standard but
perhaps not allowing easy migration from Oracle.

> In other words:
> SAVEPOINT == BEGIN NESTED
> RELEASE SAVEPOINT == COMMIT NESTED
> ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
>
> If I'm not mistaken, the above matches the functionality already coded by
> Alvaro. It begins but does not complete our compliance with SQL3 Savepoint
> syntax, putting us on the right road but making developers aware that there
> are some differences between our implementation and the standard. Thus
> developers would be able to adopt the current syntax now, and the same
> applications would still run when we complete standards-compliant syntax
> later.
>
> HOWEVER, I do still find one major flaw in Alvaro's implementation that I
> can't seem to get other people on this list to take seriously, or maybe I'm
> just not understanding the answers. One-half the point of Savepoints/Nested
> Transactions is the ability to recover from certain kinds of errors (like
> duplicate keys) inside a transaction and still commit the transaction after
> the abort condition has been rolled back.
> But the ability to detect an abort state *from the SQL command line* (or a
> database port connection) has not been addressed. I've seen some comments
> about functions to find an abort state from libpq in the text, but I'm not
> even clear if this has been coded or is just theoretical. Parsing the
> output of STDERR is *not* adequate. We need to be able to query whether we
> are in an abort state, or we make NTs absolutely useless to any client
> application that has connections which cannot, or do not yet, incorporate new
> libpq functions, something which could take considerable time after the 7.5
> release.
> Do we already have an ability to query the SQLSTATE from the command line?
> If so, what numbers indicate an abort state, if any?
> Without this issue being addressed, I will change my opinion and vote for
> option (4) because clearly the NT patch will not be ready for prime-time.

Don't we see the error from libpq PQexec() return value and other
interfaces? Are you saying how do we detect a failure from a psql
script?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sat, 10 Jul 2004, Josh Berkus wrote:

> In other words:
> SAVEPOINT == BEGIN NESTED
> RELEASE SAVEPOINT == COMMIT NESTED
> ROLLBACK TO SAVEPOINT == ROLLBACK NESTED

Here it should be:

ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;

And just to clearify, this is an extension to the spec that we then have
to support for a long time. Adding this now and then replacing it with the
standard syntax is not very sexy. If we add this we should support
it "for ever".

--
/Dennis Björklund

Dennis Bjorklund wrote:
> On Sat, 10 Jul 2004, Josh Berkus wrote:
>
> > In other words:
> > SAVEPOINT == BEGIN NESTED
> > RELEASE SAVEPOINT == COMMIT NESTED
> > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
>
> Here it should be:
>
> ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;
>
> And just to clearify, this is an extension to the spec that we then have
> to support for a long time. Adding this now and then replacing it with the
> standard syntax is not very sexy. If we add this we should support
> it "for ever".

Just to clarify, this is to allow rolling back to the same savepoint
multiple times. If we named savepoints, the new savepoint would be the
same name as the one we just rolled back.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Dennis, Bruce,

> Just to clarify, this is to allow rolling back to the same savepoint
> multiple times. If we named savepoints, the new savepoint would be the
> same name as the one we just rolled back.

Hmmm ... yeah, it would be nice to find a way around this so that we don't
have non-standard behavior we have to work around once savepoint names are
implemented. Suggestions?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian wrote:
> Dennis Bjorklund wrote:
> > On Sat, 10 Jul 2004, Josh Berkus wrote:
> >
> > > In other words:
> > > SAVEPOINT == BEGIN NESTED
> > > RELEASE SAVEPOINT == COMMIT NESTED
> > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
> >
> > Here it should be:
> >
> > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;
> >
> > And just to clearify, this is an extension to the spec that we then have
> > to support for a long time. Adding this now and then replacing it with the
> > standard syntax is not very sexy. If we add this we should support
> > it "for ever".
>
> Just to clarify, this is to allow rolling back to the same savepoint
> multiple times. If we named savepoints, the new savepoint would be the
> same name as the one we just rolled back.

Sorry, I confused folks. I should have corrected this line too:

> > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED;

It is not a non-standard behavior. It is only an implementation detail
used internally that allows nested transactions to implement savepoints.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I just posted a clarification. It isn't a problem.

---------------------------------------------------------------------------

Josh Berkus wrote:
> Dennis, Bruce,
>
> > Just to clarify, this is to allow rolling back to the same savepoint
> > multiple times. If we named savepoints, the new savepoint would be the
> > same name as the one we just rolled back.
>
> Hmmm ... yeah, it would be nice to find a way around this so that we don't
> have non-standard behavior we have to work around once savepoint names are
> implemented. Suggestions?
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce,

> Well, Oracle doesn't suppor RELEASE, so we are matching the standard but
> perhaps not allowing easy migration from Oracle.

Well, that's Oracle's problem. Considering the amount of influence they had
over the standard, there's no excuse for their syntax. Also, if someone
converts and Oracle script which does not do RELEASE, it's still ok with us;
they just end up nesting multiple levels and not "releasing" until the main
transaction is committed.

> Don't we see the error from libpq PQexec() return value and other
> interfaces?

As far as I know, DBD::pg does not at this time; it detects an error but does
not return the SQLSTATE, and I'm *sure* that PHP 4 does not. I'm sure there
are other interfaces in the same boat. And nobody has answered the question
of what SQLSTATE ranges indicate an abort state as opposed to something else
-- I get the feeling that this is not at all defined.

> Are you saying how do we detect a failure from a psql
> script?

Right. There are applications out there: shell scripts, ODBC applications,
etc., which are unlikely to *ever* have the ability to read states from
libpq. These applications need to have the ability to detect an abort *by
query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue
the proper ROLLBACKs.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Sat, 10 Jul 2004, Bruce Momjian wrote:

> > > > SAVEPOINT == BEGIN NESTED
> > > > RELEASE SAVEPOINT == COMMIT NESTED
> > > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
> > >
> > > Here it should be:
> > >
> > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;
> > >
> > > And just to clearify, this is an extension to the spec that we then have
> > > to support for a long time. Adding this now and then replacing it with the
> > > standard syntax is not very sexy. If we add this we should support
> > > it "for ever".
>
> Sorry, I confused folks. I should have corrected this line too:
>
> > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED;

Hmm, yes. Correct.

Luckily, we already had: SAVEPOINT == BEGIN NESTED so it all worked out
:-)

> It is not a non-standard behavior. It is only an implementation detail
> used internally that allows nested transactions to implement savepoints.

The non-standard part I was talking about was the savepoints without
names, and that is what we should support for ever if we introduce them.

--
/Dennis Björklund

Josh Berkus wrote:
> Bruce,
>
> > Well, Oracle doesn't suppor RELEASE, so we are matching the standard but
> > perhaps not allowing easy migration from Oracle.
>
> Well, that's Oracle's problem. Considering the amount of influence they had
> over the standard, there's no excuse for their syntax. Also, if someone
> converts and Oracle script which does not do RELEASE, it's still ok with us;
> they just end up nesting multiple levels and not "releasing" until the main
> transaction is committed.

OK.

> > Don't we see the error from libpq PQexec() return value and other
> > interfaces?
>
> As far as I know, DBD::pg does not at this time; it detects an error but does
> not return the SQLSTATE, and I'm *sure* that PHP 4 does not. I'm sure there
> are other interfaces in the same boat. And nobody has answered the question
> of what SQLSTATE ranges indicate an abort state as opposed to something else
> -- I get the feeling that this is not at all defined.

They have no way of reporting a failed query back to the user? How do
people program in those environments? Right now any failed query aborts
the transaction so it seems it would be pretty easy.

> > Are you saying how do we detect a failure from a psql
> > script?
>
> Right. There are applications out there: shell scripts, ODBC applications,
> etc., which are unlikely to *ever* have the ability to read states from
> libpq. These applications need to have the ability to detect an abort *by
> query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue
> the proper ROLLBACKs.

Well, that involves either creating a conditional capability in the
backend, or in psql, neither of which will happen for 7.5. The best we
can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
ROLLBACK) and just let the script keep going. I am thinking of cases
where you want to drop an object you aren't sure exists in a
transaction. Anything more complicated like issuing a replacement query
will have to wait for 7.6.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce,

> They have no way of reporting a failed query back to the user? How do
> people program in those environments? Right now any failed query aborts
> the transaction so it seems it would be pretty easy.

Believe it or not, PHP4 doesn't. This is one of the reasons why coders in
other languages don't consider PHP a "real" programming language; the lack of
exception handling. However, given this limitation we can't really use NTs
in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up.

To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the
DBD::Pg driver reports back a query exception, but not the SQLSTATE. This
means that we can detect an abort (assuming all exceptions are aborts) but
not what caused the abort, except by parsing the error message for text -- a
hazardous approach at best. But you would be right to point out that this is
a problem with the DBD::Pg driver.

There are, however, other client applications where the problem is more
ingrained. I've done an application for ColdFusion recently, and discovered
that CF is completely unable to detect even the limited error-reporting
capability of ODBC. This means that if CF can't query it, it doesn't exist.

> Well, that involves either creating a conditional capability in the
> backend, or in psql, neither of which will happen for 7.5. The best we
> can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
> ROLLBACK) and just let the script keep going. I am thinking of cases
> where you want to drop an object you aren't sure exists in a
> transaction. Anything more complicated like issuing a replacement query
> will have to wait for 7.6.

OK, I didn't realize that it was a difficult thing. I think it should go on
the TODO list but you are the judge of what's a quick fix and what's not.

(BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE
ERROR)

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Dennis,

> The non-standard part I was talking about was the savepoints without
> names, and that is what we should support for ever if we introduce them.

I don't have a problem with that idea. Anonymous Savepoints should be easy
to support if we are supporting Named (spec) Savepoints. And the two should
even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts
with a different syntax would. And, it's also a convenient shortcut for the
most common case -- transactions with 1 level of nesting and only a couple of
non-overlapping savepoints.

Of course, if Alvaro can knock out Named Savepoints in a week, then sure,
let's go for it. But I've not heard him saying he can.

However, this does bring up an important issue; if we implement anonymous
savepoints, then should the current implementation accept savepoint names and
just ignore them? If not, it makes porting and coding for the spec much
more difficult; if so, ported applications could develop subtle erroneous
behaviour through wrong rollbacks.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
> Bruce,
>
> > They have no way of reporting a failed query back to the user? How do
> > people program in those environments? Right now any failed query aborts
> > the transaction so it seems it would be pretty easy.
>
> Believe it or not, PHP4 doesn't. This is one of the reasons why coders in
> other languages don't consider PHP a "real" programming language; the lack of
> exception handling. However, given this limitation we can't really use NTs
> in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up.
>
> To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the
> DBD::Pg driver reports back a query exception, but not the SQLSTATE. This
> means that we can detect an abort (assuming all exceptions are aborts) but
> not what caused the abort, except by parsing the error message for text -- a
> hazardous approach at best. But you would be right to point out that this is
> a problem with the DBD::Pg driver.
>
> There are, however, other client applications where the problem is more
> ingrained. I've done an application for ColdFusion recently, and discovered
> that CF is completely unable to detect even the limited error-reporting
> capability of ODBC. This means that if CF can't query it, it doesn't exist.

Well, I don't think we need exception handling to support failed
transactions. Don't these function calls return some failure result
code?

> > Well, that involves either creating a conditional capability in the
> > backend, or in psql, neither of which will happen for 7.5. The best we
> > can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
> > ROLLBACK) and just let the script keep going. I am thinking of cases
> > where you want to drop an object you aren't sure exists in a
> > transaction. Anything more complicated like issuing a replacement query
> > will have to wait for 7.6.
>
> OK, I didn't realize that it was a difficult thing. I think it should go on
> the TODO list but you are the judge of what's a quick fix and what's not.

Adding something to psql or the backend like IF (ERROR) ... would be a
big job, I would think.

> (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE
> ERROR)

The syntax was for support of script languages that don't have
conditional constructs, like psql scripts, where you want the subxact to
commit but if it fails, you don't want that to affect the outer
transaction. Are you saying there are very few cases where you don't
care if the subxact commits or aborts?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Josh Berkus wrote:
> Dennis,
>
> > The non-standard part I was talking about was the savepoints without
> > names, and that is what we should support for ever if we introduce them.
>
> I don't have a problem with that idea. Anonymous Savepoints should be easy
> to support if we are supporting Named (spec) Savepoints. And the two should
> even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts
> with a different syntax would. And, it's also a convenient shortcut for the
> most common case -- transactions with 1 level of nesting and only a couple of
> non-overlapping savepoints.
>
> Of course, if Alvaro can knock out Named Savepoints in a week, then sure,
> let's go for it. But I've not heard him saying he can.

It seems anonymous savepoints really don't buy us anything. They don't
match the Oracle behavior, and don't do anything more than nested
transactions. I agree we want them, but I don't see the value they add
value right now.

> However, this does bring up an important issue; if we implement anonymous
> savepoints, then should the current implementation accept savepoint names and
> just ignore them? If not, it makes porting and coding for the spec much
> more difficult; if so, ported applications could develop subtle erroneous
> behaviour through wrong rollbacks.

I don't see how we can ignore the savepoint names without having our
code work unpredicatably. We could check for the most recent savepoint
name and error out if they reference any other name than the most recent
savepoint.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Josh Berkus wrote:
> But the ability to detect an abort state *from the SQL command line*
> (or a database port connection) has not been addressed.

This has existed since 7.4. If some interfaces don't expose it, fix
those interfaces.

Bruce Momjian wrote:

>
>The syntax was for support of script languages that don't have
>conditional constructs, like psql scripts, where you want the subxact to
>commit but if it fails, you don't want that to affect the outer
>transaction. Are you saying there are very few cases where you don't
>care if the subxact commits or aborts?
>
>
>
Trying to enable nested transaction on something that has no
conditionals seems strange to me. If you're writing an app so
complicated you so you need NTs, you'd probably not code is as psql script.

BTW, do we have real world examples of apps that are waiting to be
ported to pgsql, needing nested transactions? Looking at the coding
constructions used in those apps could help deciding what semantics
would help them.

Compiere comes to my mind, being oracle now, so they'd probably prefer
named savepoints.

Regards,
Andreas

Bruce Momjian wrote:
> It seems anonymous savepoints really don't buy us anything. They
> don't match the Oracle behavior, and don't do anything more than
> nested transactions. I agree we want them, but I don't see the value
> they add value right now.

The value they add is that they follow the SQL standard, which is a lot
better sell than "proprietary transaction management scheme". Those
people who think they can redefine the SQL standard for purely
aesthetic reasons have paid the price over and over again.

Bruce,

> It seems anonymous savepoints really don't buy us anything. They don't
> match the Oracle behavior, and don't do anything more than nested
> transactions. I agree we want them, but I don't see the value they add
> value right now.

Anonymous Savepoints == Nested Transactions

This issue is whether we're going to use a PostgreSQL-specific, non-standard,
syntax for NTs, or use a syntax that puts us on the road to implementing
spec-compliant savepoints.

Given that the functionality is exactly the same in either case, I don't see
why you would want to implement syntax which is 100% Postgres-specific.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

> 3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax. This would
> have the twin benefit of both allowing us to improve our standards compliance
> and make savepoints completely compliant in the next version, as well as
> helping those wishing to migrate from Oracle to PostgreSQL (currently our
> largest source of migrations). Its disadvantage is the subtle differences
> between Alvaro's patch and the standard, which might not be obvious to users
> and lead to difficult to locate errors. This option also comes in two
> flavors:
> a) we implement savepoint names, troubleshooting the namespace and scoping
> issues, which would really make this a different feature and delay beta
> testing, or
> b) we do anonymous savepoints for now, which more-or-less exactly matches the
> current behavior of Alvaro's patch, and do complaint, named savepoints in the
> next version.

As Dennis has said, whatever we do now we should support "for ever". If
we end up with compliant SAVEPOINT (eventually in 7.6+) plus some
nonstandard syntax (from 7.5), what is the nonstandard syntax you would
prefer to see? I'd prefer a syntax that reflects the primitives actually
in use i.e. BEGIN NESTED.

[...]

> In other words:
> SAVEPOINT == BEGIN NESTED
> RELEASE SAVEPOINT == COMMIT NESTED
> ROLLBACK TO SAVEPOINT == ROLLBACK NESTED

As pointed out by others ROLLBACK TO SAVEPOINT is actually ROLLBACK
NESTED + BEGIN NESTED. This means that if we only have savepoint syntax,
there is no way to do a plain rollback of a nested transaction (you have
to ROLLBACK TO SAVEPOINT foo; RELEASE SAVEPOINT foo which I find pretty
ugly and nonobvious, and it gives you an extra empty subtransaction)

> But the ability to detect an abort state *from the SQL command line* (or a
> database port connection) has not been addressed.

There is a transaction state indicator in the V3 protocol's
ReadyForQuery message. One of the states is "in aborted transaction".
It's been around since 7.4.

Whatever logic is needed for running different/conditional SQL based on
transaction state then belongs on the client side, IMO.

-O

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > It seems anonymous savepoints really don't buy us anything. They
> > don't match the Oracle behavior, and don't do anything more than
> > nested transactions. I agree we want them, but I don't see the value
> > they add value right now.
>
> The value they add is that they follow the SQL standard, which is a lot
> better sell than "proprietary transaction management scheme". Those
> people who think they can redefine the SQL standard for purely
> aesthetic reasons have paid the price over and over again.

Uh, anonymous savepoints aren't in the standard, so we aren't any closer
to the standard with them or without them, and Oracle doesn't have them
either.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Josh Berkus wrote:
> > Well, that involves either creating a conditional capability in the
> > backend, or in psql, neither of which will happen for 7.5. The best we
> > can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
> > ROLLBACK) and just let the script keep going. I am thinking of cases
> > where you want to drop an object you aren't sure exists in a
> > transaction. Anything more complicated like issuing a replacement query
> > will have to wait for 7.6.
>
> OK, I didn't realize that it was a difficult thing. I think it should go on
> the TODO list but you are the judge of what's a quick fix and what's not.
>
> (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE
> ERROR)

OK, no one likes that idea, so let's forget it.

Do we want to allow BEGIN NESTED to start a main transaction? Oracle
can use SAVEPOINTS all the time because it knows it is always in a
transaction, but PostgreSQL is not always. I don't see a downside to
allowing it. COMMIT will still commit the entire transaction, of
course.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sat, 2004-07-10 at 15:21, Josh Berkus wrote:
> Bruce,
>
> > They have no way of reporting a failed query back to the user? How do
> > people program in those environments? Right now any failed query aborts
> > the transaction so it seems it would be pretty easy.
>
> Believe it or not, PHP4 doesn't. This is one of the reasons why coders in
> other languages don't consider PHP a "real" programming language; the lack of
> exception handling. However, given this limitation we can't really use NTs
> in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up.

Uh, I think it can:

http://www.php.net/manual/en/function.pg-result-error.php

On Sat, Jul 10, 2004 at 08:25:16PM -0400, Bruce Momjian wrote:

> Do we want to allow BEGIN NESTED to start a main transaction?

I have a better question: do we allow SAVEPOINT (i.e. to establish a
savepoint, i.e. to open a nested transaction) within an aborted
transaction block?

This is allowed in nested transactions, so:

begin;
select 0/0; -- aborts
begin;
select 1; -- the usual "commands will be ignored till the end"
commit;
commit; -- it really rolls back

But in savepoints it's not clear that we want to allow to establish a
savepoint, so do you prefer

begin;
select 0/0;
savepoint foo; -- "commands will be ignored"
select 1; -- "commands will be ignored"
release foo; -- "commands will be ignored"
commit; -- it really rolls back

Or

begin;
select 0/0;
savepoint foo; -- executes it
select 1; -- "commands will be ignored"
release foo; -- executes it
commit; -- it really rolls back

There is not a lot of difference. This was allowed in nested
transactions because we wanted the nesting be to OK when using it in a
possibly aborted transaction block, so the user would not commit a
transaction that could not have been created. In savepoints it's a
nonissue because the command to end the outer xact is different.

My opinion is that we should disallow both SAVEPOINT and RELEASE when in
an aborted transaction block. Only ROLLBACK TO, ROLLBACK and COMMIT
would be allowed. In this scenario, ROLLBACK TO would always return to
a non-aborted transaction state, or the target savepoint would not
exist and the state would remain the same.

There are several places where the code could be made simpler with this.
Opinions please?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)

On Sat, 10 Jul 2004, Bruce Momjian wrote:

> Oracle can use SAVEPOINTS all the time because it knows it is always in
> a transaction, but PostgreSQL is not always.

PostgreSQL is also alsways in a transaction. If some use autocommit and go

SAVEPOINT foo;

RELEASE foo;

The first will work and that transaction will end. Then the next is in a
new transaction and will fail with an error saying that foo does not
exist. That's how it should be.

If people don't like or understand autocommit they should not use it.
This is especially true in other cases where people do updates that really
must be in a single transaction.

Your idea is that if we use nested BEGIN/COMMIT one can always issue these
even if one does not know if one have done BEGIN before or not. To me this
is a problem with autocommit that is solved by not using autocommit. I
don't think this is a problem we should solve.

--
/Dennis Björklund

Scott,

> Uh, I think it can:
>
> http://www.php.net/manual/en/function.pg-result-error.php

Heh. I half-knew that if I pointed this out that someone would correct me
with a link to new code. In my defense, I will point out that the mentioned
PHP feature is less than 4 months old.

> Not a real language indeed. :-)

<grin> I hope you relize that that was said as someone who uses PHP for a lot
of projects ...

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Bruce,

> Do we want to allow BEGIN NESTED to start a main transaction? Oracle
> can use SAVEPOINTS all the time because it knows it is always in a
> transaction, but PostgreSQL is not always. I don't see a downside to
> allowing it. COMMIT will still commit the entire transaction, of
> course.

Hmmm. I can see where this could cause trouble, allowing users and
developers to be unclear about whether or not they are in an explicit
transaction and thus leading to significant debugging issues. So I'm not
keen on, it, no.

What's the benefit? Elein?

And before you start the "function" argument: due to function autocommit, a
function is automatically part of a main implict transaction. So functions
are a non-argument as they will *always* be using NESTED/SAVEPOINT. This
would only become a concern if we started supporting non-transactional stored
procedures (ala Sybase) which nobody has even discussed working on.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Alvaro,

> I have a better question: do we allow SAVEPOINT (i.e. to establish a
> savepoint, i.e. to open a nested transaction) within an aborted
> transaction block?

My opinion? No. I would personally not want to allow it.

> My opinion is that we should disallow both SAVEPOINT and RELEASE when in
> an aborted transaction block. Only ROLLBACK TO, ROLLBACK and COMMIT
> would be allowed.

I agree.

> In this scenario, ROLLBACK TO would always return to
> a non-aborted transaction state, or the target savepoint would not
> exist and the state would remain the same.

This is also good.

From my perspective, as a builder of some *very* database-centric
applications, if one has an abort contidition that proceeds to try to
establish a Savepoint as if the abort didn't exist then one needs to do some
debugging. I'm sorry I missed the original discussion on this or I would
have expressed this opinion earlier.

For that matter:

begin;
savepoint;
select 0/0; -- abort
savepoint; -- commands will be ignored
select 1; -- commands will be ignored
release; -- commands will be ignored
release; -- abort main xact 'cause we didn't rollback
commit; -- abort message

Is the above more or less correct, Alvaro?

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Sun, 2004-07-11 at 16:01, Josh Berkus wrote:
> Scott,
>
> > Uh, I think it can:
> >
> > http://www.php.net/manual/en/function.pg-result-error.php
>
> Heh. I half-knew that if I pointed this out that someone would correct me
> with a link to new code. In my defense, I will point out that the mentioned
> PHP feature is less than 4 months old.

Actually, it's part of PHP since 4.2.0, which was released on 22 April
2002. That's long enough most folks should know of it by now.

(see http://www.php.net/releases.php)

> > Not a real language indeed. :-)
>
> <grin> I hope you relize that that was said as someone who uses PHP for a lot
> of projects ...

I know you do, I'm just amazed at how many people will dog PHP when it's
not the same language they downloaded and tried 5 years ago :-(

On Sun, Jul 11, 2004 at 03:15:46PM -0700, Josh Berkus wrote:

> For that matter:
>
> begin;
> savepoint;
> select 0/0; -- abort
> savepoint; -- commands will be ignored
> select 1; -- commands will be ignored
> release; -- commands will be ignored
> release; -- abort main xact 'cause we didn't rollback
> commit; -- abort message
>
> Is the above more or less correct, Alvaro?

Save a minor detail. It would be

begin;
savepoint;
select 0/0; -- abort
savepoint; -- commands will be ignored
select 1; -- commands will be ignored
release; -- commands will be ignored
release; -- commands will be ignored
commit; -- abort message

Note that I'm trying to tell you something with the indenting; all those
commands are inside one and the same subtransaction.

And I'm not planning to do anonymous savepoint. Do these buy us
anything?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)

Alvaro Herrera wrote:
> On Sun, Jul 11, 2004 at 03:15:46PM -0700, Josh Berkus wrote:
>
> > For that matter:
> >
> > begin;
> > savepoint;
> > select 0/0; -- abort
> > savepoint; -- commands will be ignored
> > select 1; -- commands will be ignored
> > release; -- commands will be ignored
> > release; -- abort main xact 'cause we didn't rollback
> > commit; -- abort message
> >
> > Is the above more or less correct, Alvaro?
>
> Save a minor detail. It would be
>
> begin;
> savepoint;
> select 0/0; -- abort
> savepoint; -- commands will be ignored
> select 1; -- commands will be ignored
> release; -- commands will be ignored
> release; -- commands will be ignored
> commit; -- abort message
>
> Note that I'm trying to tell you something with the indenting; all those
> commands are inside one and the same subtransaction.
>
> And I'm not planning to do anonymous savepoint. Do these buy us
> anything?

Don't bother if you can do named ones.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> To my current knowledge (and hopefully Andrew will speak up if I'm
> wrong) the DBD::Pg driver reports back a query exception, but not
> the SQLSTATE.

The current production driver will report back the error, but not the
SQLSTATE. The next version (now in cvs) will report back the SQLSTATE.
It will probably go beta in a couple of weeks and eventually become
version 1.33.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200407102121
-----BEGIN PGP SIGNATURE-----

iD8DBQFA8e5QvJuQZxSWSsgRAo5TAKDD1OX5xV4mfyUC8RAt+0SA8gbjiACeJIFV
2rZfNrm9OTFJ+/kzAjUiMJM=
=TkiX
-----END PGP SIGNATURE-----

On 7/10/2004 6:55 PM, Josh Berkus wrote:

> Bruce,
>
>> It seems anonymous savepoints really don't buy us anything. They don't
>> match the Oracle behavior, and don't do anything more than nested
>> transactions. I agree we want them, but I don't see the value they add
>> value right now.
>
> Anonymous Savepoints == Nested Transactions

Almost

>
> This issue is whether we're going to use a PostgreSQL-specific, non-standard,
> syntax for NTs, or use a syntax that puts us on the road to implementing
> spec-compliant savepoints.
>
> Given that the functionality is exactly the same in either case, I don't see
> why you would want to implement syntax which is 100% Postgres-specific.
>

I don't think they are 100% the same. The SQL3 spec defines in 7.15 and
13.4 that each sql procedure statement and each subquery on close
implicitly destroy all savepoints that have been created during that
statement or subquery.

I am however certain that nested transactions do not offer any
additional functionality that would not be available through savepoints.
So what I am missing is the reason why we would want a non-standard
syntax at all. Especially using the keyword BEGIN in the syntax would
strike me as dumb, because it will create a parsing and reading
nightmare for PL/pgSQL, since that language uses BEGIN ... END; for
grouping statements like C uses curly braces.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

On 7/11/2004 12:22 AM, Alvaro Herrera wrote:

> There is not a lot of difference. This was allowed in nested
> transactions because we wanted the nesting be to OK when using it in a
> possibly aborted transaction block, so the user would not commit a
> transaction that could not have been created. In savepoints it's a
> nonissue because the command to end the outer xact is different.
>
>
> My opinion is that we should disallow both SAVEPOINT and RELEASE when in
> an aborted transaction block. Only ROLLBACK TO, ROLLBACK and COMMIT
> would be allowed. In this scenario, ROLLBACK TO would always return to
> a non-aborted transaction state, or the target savepoint would not
> exist and the state would remain the same.

As I interpret the spec ROLLBACK TO foo will rollback all savepoints
that have been created since savepoint foo was created including ones
explicitly released. That means, that every subxid >= foo is aborted,
and a new foo subtransaction created.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #