On Fri, Jan 26, 2018 at 8:42 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:
>
> On Fri, Jan 26, 2018 at 10:32 AM, Robert Zenz <robert.z...@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.
