Re: Information on savepoint requirement within transctions - Mailing list pgsql-general

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