Re: Information on savepoint requirement within transctions

On 2018-01-29 06:36:41 -0700, David G. Johnston wrote:
> On Mon, Jan 29, 2018 at 1:37 AM, Robert Zenz <robert(dot)zenz(at)sibvisions(dot)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(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>