Re: Information on savepoint requirement within transctions

On 29.01.2018 15:11, Alban Hertroys wrote:
> IMHO, the burden of explaining that is with those RDBMSes that don't
> behave properly:
>
> If you start a transaction and something goes wrong in the process,
> the logical behaviour is to fail - the user will want to rollback to a
> sane state, doing any more work is rather pointless because of that.
> Allowing a commit at the end is dubious at best.

One could argue that automatically "undoing all broken things" (read: reverting
back to the state before the failing statement was executed) would be a feature
worth having. As far as I recall, that has also been brought up on the mailing
list. Though, I don't care particularly about it. I was just interested in the
documentation.

> That does not exclude PG from documenting this behaviour, but I'd have
> a look at the docs for those other vendors whether they perhaps
> documented their irregular transactional behaviour ;)

Heh, good luck. :)

> You didn't mention which RDBMSes behave like what you expected
> (probably from experience), but I seem to recall Oracle does odd stuff
> like that, as well as issuing a commit to all open transactions when
> any DDL happens or treating NULLs and empty literals as the same
> thing. Just to say that the "big names" aren't without flaws - they're
> kind of hard to fix when users probably depend on their behaviour
> though.

To reiterate my example (to get rid of any misconceptions):

begin transaction
insert row #1
insert row #2 (this fails)
insert row #3
commit

I've tested MySQL/MariaDB, Oracle, H2 and SQLite, all allow to continue a
transaction after a failed statement without user interaction (rows #1 and #3
are in the database after committing). PostgresSQL requires the manual rollback
to savepoint after a failed statement (obviously stores #1 and #3 in the
database if each insert is "wrapped" with a savepoint). MSSQL on the other hand
loses the complete state up to the failed statement and allows the user to
continue to use the transaction like nothing happened (only #3 is inserted when
committing). So, I think we can all agree who's the actually broken one here. ;)