On 2018-01-29 06:36:41 -0700, David G. Johnston wrote: > On Mon, Jan 29, 2018 at 1:37 AM, Robert Zenz <robert.z...@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
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
signature.asc
Description: PGP signature
