Search Postgresql Archives

Re: Information on savepoint requirement within transctions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx>
> 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@xxxxxx         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment: signature.asc
Description: PGP signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux