Re: Information on savepoint requirement within transctions - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | Re: Information on savepoint requirement within transctions |
| Date | |
| Msg-id | CAKFQuwYY+uMxpgmWm=V=E99GL8x53cY5-oBnnqGb8ZhTmcbznw@mail.gmail.com Whole thread Raw |
| In response to | Re: Information on savepoint requirement within transctions (Melvin Davidson <melvin6925@gmail.com>) |
| Responses |
Re: Information on savepoint requirement within transctions
(Robert Zenz <robert.zenz@sibvisions.com>)
|
| List | pgsql-general |
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.
pgsql-general by date: