Обсуждение: 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?
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?
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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 >
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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 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-transact ions.html
>
> https://www.postgresql.org/docs/9.6/static/sql-savepoint.htm l
>
> https://www.postgresql.org/docs/9.6/static/sql-rollback-to. html
>
> https://www.postgresql.org/docs/9.6/static/sql-release-savep oint.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 afterthe 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.
"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.
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:
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
>As far as I'm aware neither PostgreSQL nor OS version do matter for thisYes as of this date. However, that is not to say that the SQL standard (or PostgreSQL) may changein 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 willhave 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
On Fri, Jan 26, 2018 at 11:41 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>As far as I'm aware neither PostgreSQL nor OS version do matter for thisYes as of this date. However, that is not to say that the SQL standard (or PostgreSQL) may changein 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 willhave 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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
> ...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.
Do you really think op's look for the timestamp of answers as opposed to content?
>The timestamp on the email is likely more than sufficient
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 youthe 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.
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
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.
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" <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?
My turn to fail to re-read the docs :(
David J.
> 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.
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.
David J.
diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml
index c23bbfb4e7..c1b3ef9306 100644
--- a/doc/src/sgml/ref/begin.sgml
+++ b/doc/src/sgml/ref/begin.sgml
@@ -49,6 +49,16 @@ BEGIN [ WORK | TRANSACTION ] [ <replaceable class="parameter">transaction_mode</
</para>
<para>
+ Pseudo sub-transactions are created using <xref linkend="sql-savepoint"/>.
+ 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. The server cannot be configured to do this
+ automatically: all (sub-)transaction blocks either commit or rollback in their
+ entirety. A commit issued while the transaction has an active failure
+ is automatically converted into a <xref linkend="sql-rollback"/>.
+ </para>
+
+ <para>
Statements are executed more quickly in a transaction block, because
transaction start/commit requires significant CPU and disk
activity. Execution of multiple statements inside a transaction is
diff --git a/doc/src/sgml/ref/commit.sgml b/doc/src/sgml/ref/commit.sgml
index b2e8d5d180..8bb368b771 100644
--- a/doc/src/sgml/ref/commit.sgml
+++ b/doc/src/sgml/ref/commit.sgml
@@ -29,9 +29,11 @@ COMMIT [ WORK | TRANSACTION ]
<title>Description</title>
<para>
- <command>COMMIT</command> commits the current transaction. All
+ <command>COMMIT</command> ends the current transaction. All
changes made by the transaction become visible to others
- and are guaranteed to be durable if a crash occurs.
+ and are guaranteed to be durable if a crash occurs. However,
+ if the transaction has failed a <xref linkend="sql-rollback"/>
+ will be processed instead.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/savepoint.sgml b/doc/src/sgml/ref/savepoint.sgml
index 87243b1d20..66cee63966 100644
--- a/doc/src/sgml/ref/savepoint.sgml
+++ b/doc/src/sgml/ref/savepoint.sgml
@@ -41,7 +41,8 @@ SAVEPOINT <replaceable>savepoint_name</replaceable>
<para>
A savepoint is a special mark inside a transaction that allows all commands
that are executed after it was established to be rolled back, restoring
- the transaction state to what it was at the time of the savepoint.
+ the transaction state to what it was at the time of the savepoint. It can be
+ thought of as a kind of a pseudo sub-transaction.
</para>
</refsect1>
@@ -74,6 +75,11 @@ SAVEPOINT <replaceable>savepoint_name</replaceable>
Savepoints can only be established when inside a transaction block.
There can be multiple savepoints defined within a transaction.
</para>
+
+ <para>
+ <application>psql</application> makes use of savepoints to implment its
+ <literal>ON_ERROR_ROLLBACK</literal> behavior.
+ </para>
</refsect1>
<refsect1>
Вложения
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.
>
> 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.
I was using failed because I hadn't done sufficient research and wasn't aware of "aborted" being used in this context. The error in psql itself says "current transaction is aborted ..."
There is no distinction as to why the statements failed and the transaction is in an aborted state as far as a transaction is concerned.
David J.
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 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.
Given three options, and re-reading the paragraph, I figured dropping the last part altogether was probably the best; though of the three "continue to use a transaction after a failure" was close.
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"/>.
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.
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.
A title and a paragraph or two on aborted transaction behavior probably should be added as well.
Not compiled, not sure how the tutorial modifications would (want to) interplay with the table of contents.
Diff from v1 and full v2 diff attached.
David J.
Вложения
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.