Thread: Information on savepoint requirement within transctions

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?

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
>

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 26.01.2018 16:42, Melvin Davidson wrote:
> 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.
> 
>

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 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 29.01.2018 16:33, Tom Lane wrote:
> 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.

Very interesting. However, I'm talking explicitly about the behavior that occurs
when AUTOCOMMIT is switched off and a statement fails.

Most curiously, you already did such a feature (of what I was talking about)
request in 2007:
https://www.postgresql.org/message-id/flat/11539.1177352713%40sss.pgh.pa.us#11539.1177352713@sss.pgh.pa.us

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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.

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

Which part of that isn't implemented by ON_ERROR_ROLLBACK?

            regards, tom lane

David G. Johnston wrote:
> > > It may be worth updating the docs here...
> > 
> So, my first pass at this.  I'm probably going a bit outside what would normally be covered in the SQL
> Command section but it does feel right at first blush.
> 
> Also attached; not compiled.
> 
> As a bug fix I've updated the description of "COMMIT" here since it can cause a ROLLBACK to be issued and that isn't
documented.
> 
> "(pseudo) sub-transaction" seemed like a reasonable choice of terminology to introduce rather than just "mark".
> Having it mentioned in context in the BEGIN docs, instead of just a "see also", should aid in understanding
> how the whole transaction system fits together.  The advanced features of the tutorial cover this to some degree
> (I didn't re-read it prior to writing this up) but I'm inclined to believe people wanting to understand
transactions,
> lacking a top-level chapter on the topic, will know of BEGIN and start their discovery there.

I think that it is a good idea to explain the behavior of aborted transactions.
Shouldn't that go to the hackers list though?

I don't like the term "pseudo sub-transaction".  What's pseudo about it?

> +
> +  <para>
> +    <application>psql</application> makes use of savepoints to implment its
> +    <literal>ON_ERROR_ROLLBACK</literal> behavior.
> +  </para>

s/implment/implement/

Yours,
Laurenz Albe

On 30.01.2018 03:07, David G. Johnston wrote:
 > ​So, my first pass at this.

Nice, thank you.

 > + These are of particular use for client software to use when executing
 > + user-supplied SQL statements and want to provide try/catch behavior
 > + where failures are ignored.

Personally, I'd reword this to something like this:

 > These are of particular use for client software which is executing
 > user-supplied SQL statements and wants to provide try/catch behavior
 > with the ability to continue to use the transaction after a failure.

Or maybe something like this:

 > These are of particular use for client software which requires
 > fine-grained support over failure behavior within a transaction.
 > They allow to provide a try/catch behavior with the ability
 > to continue to use a transaction after a failure.

Also I'd like to see something like this in the docs at roughly the same position:

 > If a failure occurs during a transaction, the transaction enters
 > an aborted state. An aborted or failed transaction cannot be used
 > anymore to issue more commands, ROLLBACK or ROLLBACK TO must be used
 > to regain control of the aborted transaction. A commit issued while
 > the transaction is aborted is automatically converted into a
 > <xref linkend="sql-rollback"/>.

I'm not sure about the terminology here, though, because the Transaction
Tutorial (https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html)
speaks of "aborted" transactions, while you use the term "failed" here.

>
> I'm not sure about the terminology here, though, because the Transaction
> Tutorial (https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html)
> speaks of "aborted" transactions, while you use the term "failed" here.

Purely from a user point of view, shouldn't "aborted" mean a ROLLBACK issues by the application
due to a violation of a business rule, whereas "failed" should mean as a ROLLBACK issues by
PG due to constraint violation or like disk full or whatever.

On 2018-01-29 06:36:41 -0700, David G. Johnston wrote:
> 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.

I think the first (and possibly last) question should be answered in
user-facing documentation. To understand something I don't just need to
know how something works, but also why it works that way. This is
especially the case if the behaviour differs from similar systems.

I think the reason for the behaviour of Oracle, MySQL, etc. is that a
an error in an SQL statement is not necessarily an error in business
logic. One might reasonably write code like this:

    begin transaction
    ...
    insert into foo ...
    if error == unique_key_violation:
        select id from foo where ...
        update foo where id=...
    ...
    commit;

Indeed I'm quite sure that I have written something like this several
times. I have also tested tables or columns for existence simply by
selecting from them.

The reason for the PostgreSQL behaviour is probably because it is safer:
If it's the programmer's responsibility to test for the failure of
individual SQL statements, it is quite likely that the programmer
forgets a check and commits inconsistent data. This can't happen with
the PostgreSQL model. If the programmer wants to tolerate and error,
they have to handle it explicitely (with a savepoint or even a full
transaction).

I can't really think of a reason why the MSSQL behaviour might be
useful, but I'm sure that they had a use-case in mind when they designed
this.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

On 2018-01-30 08:40:11 +0000, Robert Zenz wrote:
> On 30.01.2018 03:07, David G. Johnston wrote:
>  > So, my first pass at this.
>
> Nice, thank you.
>
>  > + These are of particular use for client software to use when executing
>  > + user-supplied SQL statements and want to provide try/catch behavior
>  > + where failures are ignored.
>
> Personally, I'd reword this to something like this:
>
>  > These are of particular use for client software which is executing
>  > user-supplied SQL statements and wants to provide try/catch behavior
>  > with the ability to continue to use the transaction after a failure.
>
> Or maybe something like this:
>
>  > These are of particular use for client software which requires
>  > fine-grained support over failure behavior within a transaction.
>  > They allow to provide a try/catch behavior with the ability
>  > to continue to use a transaction after a failure.

I agree. The goal isn't to ignore the error but to handle it.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

On 31.01.2018 19:58, David G. Johnston wrote:
> ​Now that I've skimmed the tutorial again I think pointing the reader of
> the SQL Commands there to learn how it works in practice is better than
> trying to explain it in BEGIN and/or SAVEPOINT.

That seems like a good idea, yeah.

> I decided to add a title to the part of SAVEPOINTS and introduce the term
> "Sub-Transaction" there though I'm not married to it - re-wording it using
> only "savepoint" is something that should be tried still.

Technically, it *is* a sub-transaction, Savepoints are just the means to do it.
I think that a sub-transaction is the concept, Savepoint is the implementation.

> A title and a paragraph or two on aborted transaction behavior probably
> should be added as well.

I'd like that. I might be able to type something up, though I'm currently a
little bit short on time, so don't wait for me please.

Just to make sure, you have two typos in there, "61: tranasctions" and "106:
implment". Also I'd like to take the opportunity to agree with Laurenz here,
"pseudo" seems to be misplaced, they *are* sub-transactions.