Information on savepoint requirement within transctions

On Fri, Jan 26, 2018 at 9:47 AM, Robert Zenz <robert.zenz@sibvisions.com>
wrote:

I'm currently doing a small writeup of a bug fix in our framework which
involves
savepoints in PostgreSQL (JDBC). However, I have a hard time locating the
documentation regarding this. I mean, from what I can extract from various
sources, PostgreSQL requires to use savepoints if one wants to continue a
transaction after a failed statement, but I can't find where in the
documentation that is stated and documented.

Can somebody point me to the correct location where this is documented and
maybe
even explained why that is the case?

You have not specified which version of PostgreSQL you are using (or your
O/S), but is this the documention you are looking for?

https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html

https://www.postgresql.org/docs/9.6/static/sql-savepoint.html

https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html

https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Well, no. What I'm looking for is information on how the transactions behave in
an error case, and why there is the requirement to have a savepoint in place to
be able to continue a transaction after a failed statement.

As far as I'm aware neither PostgreSQL nor OS version do matter for this, I'm
interested in the general behavior of the database. But as I said, I do find a
lot of documentation on transactions in general, but not about their behavior in
an error case.

Your first link is "kinda" what I'm looking for, because it closes with

Moreover, ROLLBACK TO is the only way to regain control of a transaction

block that was put in aborted state by the system due to an error, short of
rolling it back completely and starting again.

and I'm looking on more information on *that*.

On Fri, Jan 26, 2018 at 10:32 AM, Robert Zenz <robert.zenz@sibvisions.com>
wrote:

Well, no. What I'm looking for is information on how the transactions
behave in
an error case, and why there is the requirement to have a savepoint in
place to
be able to continue a transaction after a failed statement.

As far as I'm aware neither PostgreSQL nor OS version do matter for this,
I'm
interested in the general behavior of the database. But as I said, I do
find a
lot of documentation on transactions in general, but not about their
behavior in
an error case.

Your first link is "kinda" what I'm looking for, because it closes with

Moreover, ROLLBACK TO is the only way to regain control of a transaction

block that was put in aborted state by the system due to an error, short of
rolling it back completely and starting again.

and I'm looking on more information on *that*.

On 26.01.2018 15:59, Melvin Davidson wrote:

On Fri, Jan 26, 2018 at 9:47 AM, Robert Zenz <robert.zenz@sibvisions.com

wrote:

I'm currently doing a small writeup of a bug fix in our framework which
involves
savepoints in PostgreSQL (JDBC). However, I have a hard time locating

the

documentation regarding this. I mean, from what I can extract from

various

sources, PostgreSQL requires to use savepoints if one wants to continue

a

transaction after a failed statement, but I can't find where in the
documentation that is stated and documented.

Can somebody point me to the correct location where this is documented

and

maybe
even explained why that is the case?

You have not specified which version of PostgreSQL you are using (or your
O/S), but is this the documention you are looking for?

https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html

https://www.postgresql.org/docs/9.6/static/sql-savepoint.html

https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html

https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html

Simply put, a SAVEPOINT does not allow you to "continue" a transaction
after an error.
What is does is allow you to commit everything up to the SAVEPOINT.
Everything after
the SAVEPOINT is not committed. There is no such thing as ROLLBACK THEN
CONTINUE,
which is what I think you are looking for.

Once again, please remember to specify your PostgreSQL version and O/S when
addressing this forum.
It helps to clarify solutions for historical purposes.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

What I'm looking for is more information/documentation on that topic that I can
use as source and link back to (from a blog post).

That last paragraph in your first link is exactly what I meant. Let's start to
clarify things, put into pseudo code:

start transaction
insert into A
insert into B but fail
insert into C
commit

As far as I'm aware, in most other databases this would work like that, even
though one statement has failed, one can continue using this transaction and
actually commit the rows in A and C. In PostgreSQL the use of savepoints is
required:

start transaction
insert into A
create savepoint
insert into B but fail
rollback to savepoint
insert into C
commit

Otherwise the transaction is, after the failed statement, in a state in which it
can not be used anymore. Is that correct?

On Fri, Jan 26, 2018 at 8:42 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

On Fri, Jan 26, 2018 at 10:32 AM, Robert Zenz <robert.zenz@sibvisions.com>
wrote:

​The convention for these lists is to inline or bottom-post. Top-posting
is discouraged.
​

Well, no. What I'm looking for is information on how the transactions

behave in

an error case, and why there is the requirement to have a savepoint in

place to
be able to continue a transaction after a failed statement.

Here's my take, the docs support this but maybe take some interpretation...

A SAVEPOINT ​begins what is effectively a sub-transaction without ending
the main transaction. If that sub-transaction fails you can throw it away
(ROLLBACK TO) and pretend that it didn't happen: you are dropped back to
the point where the savepoint was issued and the main transaction
re-engaged.

Its kinda like a try/catch block:

BEGIN:

do_stuff

SAVEPOINT try { lets_fail; this_works; } catch { ROLLBACK TO }

do_more_stuff

​COMMIT;​

​As ​long as both do_stuff and do_more_stuff succeed when you commit the
things that they did will persist.

The stuff in lets_fail AND this_works, however, will be discarded because
of the lets_fail failing and this_works belonging to the same
sub-transaction.

​If do_more_stuff depends on lets_fail or this_works succeeding then
do_more_stuff will ​fail and will cause do_stuff to rollback as well.

As far as I'm aware neither PostgreSQL nor OS version do matter for this

​You are correct.

You have not specified which version of PostgreSQL you are using (or your

O/S), but is this the documention you are looking for?

https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html

https://www.postgresql.org/docs/9.6/static/sql-savepoint.html

https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html

https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html

Simply put, a SAVEPOINT does not allow you to "continue" a transaction
after an error.

​Yes it does.​

What is does is allow you to commit everything up to the SAVEPOINT.
Everything after
the SAVEPOINT is not committed. There is no such thing as ROLLBACK THEN
CONTINUE,
which is what I think you are looking for.

​SAVEPOINTS do not commit.​

Your comments indicate that you should probably re-read the docs to which
you are referring. I haven't really used savepoints myself but the docs
describe reasonably well how they function.

https://www.postgresql.org/docs/10/static/sql-rollback-to.html

"The savepoint remains valid and can be rolled back to again later, if
needed."

That alone requires that the main transaction remain in force, that you can
add new commands to it, and that if any of them fail you can ROLLBACK TO
SAVEPOINT again.

Once again, please remember to specify your PostgreSQL version and O/S when

addressing this forum.
It helps to clarify solutions for historical purposes.

​Rarely if ever, which is why pretty much no one but you asks for it unless
it is needed. You just picked up a habit during your years in tech support
and apply them to your responses on these lists without consideration as
whether it is valid or not. I'd give you a bit of benefit of the doubt if
you limited your requests to true bug reports, and maybe -performance, but
the vast majority of -general questions do notdepend on knowing the version
and even fewer need to know the O/S.
​
David J.

On Fri, Jan 26, 2018 at 8:57 AM, Robert Zenz <robert.zenz@sibvisions.com>
wrote:

In PostgreSQL the use of savepoints is
required:

start transaction
insert into A
create savepoint
insert into B but fail
rollback to savepoint
insert into C
commit

Otherwise the transaction is, after the failed statement, in a state in
which it
can not be used anymore. Is that correct?

​Yes.

David J.
​

As far as I'm aware neither PostgreSQL nor OS version do matter for this

Yes as of this date. However, that is not to say that the SQL standard (or
PostgreSQL) may change
in the _future_, such that there "may" be a "ROLLBACK TO SAVEPOINT OR
CONTINUE" after a failure,
in which case, someone else may be confused because there is no reference
to the actual PostgreSQL version.
That is why it is important to include the version "AT THE TIME OF POSTING"
so that future op's will
have a point of reference for the answer.

On Fri, Jan 26, 2018 at 11:12 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Fri, Jan 26, 2018 at 8:57 AM, Robert Zenz <robert.zenz@sibvisions.com>
wrote:

In PostgreSQL the use of savepoints is
required:

start transaction
insert into A
create savepoint
insert into B but fail
rollback to savepoint
insert into C
commit

Otherwise the transaction is, after the failed statement, in a state in
which it
can not be used anymore. Is that correct?

​Yes.

David J.
​

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Fri, Jan 26, 2018 at 9:27 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

As far as I'm aware neither PostgreSQL nor OS version do matter for this

Yes as of this date. However, that is not to say that the SQL standard (or
PostgreSQL) may change
in the _future_, such that there "may" be a "ROLLBACK TO SAVEPOINT OR
CONTINUE" after a failure,
in which case, someone else may be confused because there is no reference
to the actual PostgreSQL version.
That is why it is important to include the version "AT THE TIME OF
POSTING" so that future op's will
have a point of reference for the answer.

So preface your answers with: "as of 10.0 this is what I understand"

The timestamp on the email is likely more than sufficient for someone to
lookup the PostgreSQL version should the need arise.

I'm not saying the presence of a version is bad, only the badgering of
people asking questions to provide it when it has no bearing on the answer
to the question being asked at the time it is asked. You are more than
welcome to meets the needs of future readers by anchoring every response
you choose to make to the version or version you know your answers apply to.

David J.

...the presence of a version is bad, only the badgering of people asking

questions to provide it when it has no bearing on the answer...

Really? Is it that hard for someone to provide version and O/S?

The timestamp on the email is likely more than sufficient

Do you really think op's look for the timestamp of answers as opposed to
content?
As someone that spent 8 years in high level tech support for Ingres (the
predecessor to Postgres), I assure you
the inclusion of version and O/S is critical for historical purpose.

On Fri, Jan 26, 2018 at 11:41 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Fri, Jan 26, 2018 at 9:27 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

As far as I'm aware neither PostgreSQL nor OS version do matter for this

Yes as of this date. However, that is not to say that the SQL standard
(or PostgreSQL) may change
in the _future_, such that there "may" be a "ROLLBACK TO SAVEPOINT OR
CONTINUE" after a failure,
in which case, someone else may be confused because there is no reference
to the actual PostgreSQL version.
That is why it is important to include the version "AT THE TIME OF
POSTING" so that future op's will
have a point of reference for the answer.

So preface your answers with: "as of 10.0 this is what I understand"

The timestamp on the email is likely more than sufficient for someone to
lookup the PostgreSQL version should the need arise.

I'm not saying the presence of a version is bad, only the badgering of
people asking questions to provide it when it has no bearing on the answer
to the question being asked at the time it is asked. You are more than
welcome to meets the needs of future readers by anchoring every response
you choose to make to the version or version you know your answers apply to.

David J.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Fri, Jan 26, 2018 at 9:47 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

...the presence of a version is bad, only the badgering of people asking

questions to provide it when it has no bearing on the answer...

Really? Is it that hard for someone to provide version and O/S?

​Its difficult to remember to include it when it should make no difference
in receiving an answer the question being asked today.

The timestamp on the email is likely more than sufficient

Do you really think op's look for the timestamp of answers as opposed to
content?

Maybe not, but in 5 years if someone pulls up this thread and sees that the
OP was talking about 9.6 what are they supposed to do with that
information? They would have no way to know whether things have changed
between 9.6 and 15. Its more in their face and they might be more
skeptical but they are still going to try using the information if the
topic seems similar.

As someone that spent 8 years in high level tech support for Ingres (the
predecessor to Postgres), I assure you
the inclusion of version and O/S is critical for historical purpose.

​The -general mailing list is not tech support, its a social forum. -bugs
is tech support and for that list the community does indeed post a request
that version and O/S information be provided, and even has a form-field to
be filled in.

If you feel so strongly then by all means add version and O/S information
to all of your responses. I'm doubtful it will generally be of benefit and
even less certain that having that information appear on the 3rd email (and
maybe only the third if it gets trimmed away during responses) in the
thread would be an improvement. One cannot control the initial email and
by the time you ask and give an answer anyway the second one is gone and
the thread may be done with (if one provides sufficient and correct
advice). Having been given an answer I doubt most people would reply:
"thanks, and for the record my version and O/S is such-and-such". Besides,
they may still be on 9.3 while the response covers 9.3-10; it is the
version of the response that would seem to matter more than the version the
questioner happens to be using and fails to provide up-front anyway.

David J.

On 26.01.2018 17:11, David G. Johnston wrote:

​The convention for these lists is to inline or bottom-post. Top-posting
is discouraged.

Okay, I'll keep it in mind.

Here's my take, the docs support this but maybe take some interpretation...

A SAVEPOINT ​begins what is effectively a sub-transaction without ending
the main transaction. If that sub-transaction fails you can throw it away
(ROLLBACK TO) and pretend that it didn't happen: you are dropped back to
the point where the savepoint was issued and the main transaction
re-engaged.

Its kinda like a try/catch block:

BEGIN:

do_stuff

SAVEPOINT try { lets_fail; this_works; } catch { ROLLBACK TO }

do_more_stuff

​COMMIT;​

​As ​long as both do_stuff and do_more_stuff succeed when you commit the
things that they did will persist.

The stuff in lets_fail AND this_works, however, will be discarded because
of the lets_fail failing and this_works belonging to the same
sub-transaction.

​If do_more_stuff depends on lets_fail or this_works succeeding then
do_more_stuff will ​fail and will cause do_stuff to rollback as well.

Thanks for the detailed explanation, that helps a lot. However, I'm still
looking for "official" documentation on their *requirement* in combination with
failing statements. Documentation, bug report, mailing list discussions,
something like that. In particular I'm interested in the questions:

* Why are they required in combination with failing statements (when every
other database does an "automatic savepoint and rollback" for a failed statement)?
* When was that behavior chosen? Was it always like that? Was it adopted later?
* What is the design decision behind it?

There is a lot of information on what savepoints are and how they work (and also
thanks to you I'm now fairly certain I have good grasp on them), but I fail to
locate documentation on these questions.

�* Why are they required in combination with failing statements (when
every other database does an "automatic savepoint and rollback" for a
failed statement)?

It this statement true? �I very much feel *not*. �This depends on how
you have set AUTO_COMMIT - and it certainly is not true for
transactions of multiple statements.

Informix does not do�"automatic savepoint and rollback" - you will
rollback the entire transaction.

On 29.01.2018 12:37, Adam Tauno Williams wrote:

It this statement true? I very much feel *not*. This depends on how
you have set AUTO_COMMIT - and it certainly is not true for
transactions of multiple statements.

Maybe I should clarify at that point that AUTO_COMMIT is OFF, and yes, that is
how it does behave for me.

Informix does not do "automatic savepoint and rollback" - you will
rollback the entire transaction.

Okay, not *all* of them, but *most* as it seems.

On Mon, Jan 29, 2018 at 1:37 AM, Robert Zenz <robert.zenz@sibvisions.com>
wrote:

Documentation, bug report, mailing list discussions,
something like that. In particular I'm interested in the questions:

* Why are they required in combination with failing statements (when every
other database does an "automatic savepoint and rollback" for a failed
statement)?
* When was that behavior chosen? Was it always like that? Was it adopted
later?
* What is the design decision behind it?

There is a lot of information on what savepoints are and how they work
(and also
thanks to you I'm now fairly certain I have good grasp on them), but I
fail to
locate documentation on these questions.

​Those questions would not be answered in user-facing documentation. You
can explore the git history and search the far-back mailing list archives
if you wish to satisfy your curiosity. For me this is how it works - the
only question for me is whether I should argue that the behavior should be
changed. I do vaguely recall this topic coming up in the recent (couple of
years) past...but changing transaction behavior like this is problematic no
matter how desirable the new state might be to have (and that's debatable).

It may be worth updating the docs here but you have received your official
answer - I'm nearly positive I'm right and even if I was mistaken most
likely I would have been corrected by now. I am writing this on a mailing
list...

David J.

On 29.01.2018 14:36, David G. Johnston wrote:

​Those questions would not be answered in user-facing documentation. You
can explore the git history and search the far-back mailing list archives if
you wish to satisfy your curiosity. For me this is how it works - the only
question for me is whether I should argue that the behavior should be
changed. I do vaguely recall this topic coming up in the recent (couple of
years) past...but changing transaction behavior like this is problematic no
matter how desirable the new state might be to have (and that's debatable).

From my point of view, no, it shouldn't be changed. It has always been this way
and I find nothing wrong with the approach, it is only something that you need
to be aware of, that's all.

It may be worth updating the docs here...

I'd vote for that. I would have expected to see this mentioned in the
documentation a little bit more prominent than just a single sentence at the end
of the transaction tutorial. A short section about how the transaction behaves
in an error cases (and what to do) would be nice.

...but you have received your official answer - I'm nearly positive I'm right
and even if I was mistaken most likely I would have been corrected by now. I
am writing this on a mailing list...

David J.

Thank you for your time and explanations.

On 29 January 2018 at 14:59, Robert Zenz <robert.zenz@sibvisions.com> wrote:

On 29.01.2018 14:36, David G. Johnston wrote:

...

From my point of view, no, it shouldn't be changed. It has always been this way
and I find nothing wrong with the approach, it is only something that you need
to be aware of, that's all.

It may be worth updating the docs here...

I'd vote for that. I would have expected to see this mentioned in the
documentation a little bit more prominent than just a single sentence at the end
of the transaction tutorial. A short section about how the transaction behaves
in an error cases (and what to do) would be nice.

IMHO, the burden of explaining that is with those RDBMSes that don't
behave properly:

If you start a transaction and something goes wrong in the process,
the logical behaviour is to fail - the user will want to rollback to a
sane state, doing any more work is rather pointless because of that.
Allowing a commit at the end is dubious at best.

That does not exclude PG from documenting this behaviour, but I'd have
a look at the docs for those other vendors whether they perhaps
documented their irregular transactional behaviour ;)

You didn't mention which RDBMSes behave like what you expected
(probably from experience), but I seem to recall Oracle does odd stuff
like that, as well as issuing a commit to all open transactions when
any DDL happens or treating NULLs and empty literals as the same
thing. Just to say that the "big names" aren't without flaws - they're
kind of hard to fix when users probably depend on their behaviour
though.

Alban Hertroys
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On 29.01.2018 15:11, Alban Hertroys wrote:

IMHO, the burden of explaining that is with those RDBMSes that don't
behave properly:

If you start a transaction and something goes wrong in the process,
the logical behaviour is to fail - the user will want to rollback to a
sane state, doing any more work is rather pointless because of that.
Allowing a commit at the end is dubious at best.

One could argue that automatically "undoing all broken things" (read: reverting
back to the state before the failing statement was executed) would be a feature
worth having. As far as I recall, that has also been brought up on the mailing
list. Though, I don't care particularly about it. I was just interested in the
documentation.

That does not exclude PG from documenting this behaviour, but I'd have
a look at the docs for those other vendors whether they perhaps
documented their irregular transactional behaviour ;)

Heh, good luck. :)

You didn't mention which RDBMSes behave like what you expected
(probably from experience), but I seem to recall Oracle does odd stuff
like that, as well as issuing a commit to all open transactions when
any DDL happens or treating NULLs and empty literals as the same
thing. Just to say that the "big names" aren't without flaws - they're
kind of hard to fix when users probably depend on their behaviour
though.

To reiterate my example (to get rid of any misconceptions):

begin transaction
insert row #1
insert row #2 (this fails)
insert row #3
commit

I've tested MySQL/MariaDB, Oracle, H2 and SQLite, all allow to continue a
transaction after a failed statement without user interaction (rows #1 and #3
are in the database after committing). PostgresSQL requires the manual rollback
to savepoint after a failed statement (obviously stores #1 and #3 in the
database if each insert is "wrapped" with a savepoint). MSSQL on the other hand
loses the complete state up to the failed statement and allows the user to
continue to use the transaction like nothing happened (only #3 is inserted when
committing). So, I think we can all agree who's the actually broken one here. ;)

Robert Zenz <robert.zenz@sibvisions.com> writes:

On 29.01.2018 15:11, Alban Hertroys wrote:

If you start a transaction and something goes wrong in the process,
the logical behaviour is to fail - the user will want to rollback to a
sane state, doing any more work is rather pointless because of that.
Allowing a commit at the end is dubious at best.

One could argue that automatically "undoing all broken things" (read: reverting
back to the state before the failing statement was executed) would be a feature
worth having.

That behavior does exist, and so does documentation for it; you're just
looking in the wrong place.

Years ago (7.3 era, around 2002) we experimented with a server-side
GUC variable "AUTOCOMMIT", which switched from the implicit-commit-
if-you-don't-say-BEGIN behavior to implicitly-issue-BEGIN-so-you-
have-to-say-COMMIT-explicitly. That was an unmitigated disaster:
flipping the setting broke just about all client applications. After
spending many months trying to fix just the apps we ship with Postgres,
and getting pushback from users whose code broke with the alternate
setting, we gave up and removed the feature. Instead we set project
policy that if you want to modify transactional behavior you have to
do it on the client side, where it doesn't risk breaking other apps.
Institutional memory around here is very long, so any time proposals to
change the server or wire-protocol behavior in this area come up, they
get batted down.

What we do have though is client-side support for appropriate behaviors.
In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.
Other interfaces such as JDBC have their own ideas about how this ought
to work.

regards, tom lane

On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

What we do have though is client-side support for appropriate behaviors.
In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.
Other interfaces such as JDBC have their own ideas about how this ought
to work.

​Not quite the same. I think what people probably want is for psql to
recognize it is in a transaction and before sending a command to the server
for processing to precede it by sending "SAVEPOINT random()". Then, before
returning the result of the command to the user issue either "RELEASE
SAVEPOINT" or "ROLLBACK TO SAVEPOINT" depending on whether the command
succeeded or failed. Then report the result to the user.

Having a GUC to instruct the server to do that instead sounds appealing as
a user, or middle-ware writer, though I couldn't see doing it given today's
GUC mechanics for the same reason the AUTOCOMMIT GUC was removed.

David J.