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

From Peter J. Holzer
Subject Re: Information on savepoint requirement within transctions
Date
Msg-id 20180131180640.k56anmhgnfxy3oin@hjp.at
Whole thread Raw
In response to Re: Information on savepoint requirement within transctions  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
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/>

Attachment

pgsql-general by date:

Previous
From: Eugene Pirogov
Date:
Subject: Issue with WAL logs temporary not replaying
Next
From: "Peter J. Holzer"
Date:
Subject: Re: Information on savepoint requirement within transctions