Re: Nested Transactions, Abort All

ISTM - my summary would be
1. We seem to agree we should support SAVEPOINTs

2. We seem to agree that BEGIN/COMMIT should stay unchanged...

> With savepoints, it looks pretty strange:
>
> BEGIN;
> SAVEPOINT x1;
> INSERT INTO ...;
> SAVEPOINT x2;
> INSERT INTO ...;
> SAVEPOINT x3;
> INSERT INTO ...;
>

This isn't how you would use SAVEPOINTs...look at this...

BEGIN
display one screen to user - book the flight
INSERT INTO ...
INSERT INTO ...
UPDATE ...
SAVEPOINT
display another related screen - book the hotel
INSERT INTO
DELETE
UPDATE
UPDATE
SAVEPOINT
offer confirmation screen
COMMIT (or ROLLBACK)

RELEASE SAVEPOINT isn't used that often...

> or with RELEASE:
>
> BEGIN;
> SAVEPOINT x1;
> INSERT INTO ...;
> RELEASE SAVEPOINT x1;
> SAVEPOINT x1;
> INSERT INTO ...;
> RELEASE SAVEPOINT x1;
> SAVEPOINT x1;
> INSERT INTO ...;
> RELEASE SAVEPOINT x1;
>

We need to be careful to differentiate from the statement-level abort
behaviour of other RDBMS and the behaviour of SAVEPOINT. It is
theoretically possible to implement them both using nested transactions,
but that doesn't mean they're the same thing.

If a statement has an error, then PostgreSQL currently rolls back the
entire transaction (transaction level abort). It would be good if this
was not the behaviour, since other programs written for other RDBMS do
NOT exhibit this behaviour - this matters a lot if/when statements
error. This behaviour MUST happen implicitly, without additional SQL
statements, otherwise its not the same behaviour. The effect is AS IF
the user had issued the sequence of statements shown above - but they do
not ACTUALLY issue those, hence the reason why the above sequences look
a little wierd.

In the current syntax we're discussing, Oracle's behaviour looks like
this (with all statements in brackets being issued implicitly...) -
using the same example I gave above

BEGIN
(SUBBEGIN)
(SUBBEGIN)
INSERT INTO ...
(SUBCOMMIT)
(SUBBEGIN)
INSERT INTO ...
(SUBCOMMIT)
(SUBBEGIN)
UPDATE ...
(SUBCOMMIT)
SUBCOMMIT
(SUBBEGIN)
(SUBBEGIN)
INSERT INTO
(SUBCOMMIT)
(SUBBEGIN)
DELETE
(SUBCOMMIT)
(SUBBEGIN)
UPDATE
(SUBCOMMIT)
(SUBBEGIN)
UPDATE
(SUBCOMMIT)
SUBCOMMIT
COMMIT (or ROLLBACK)

Note that you CANNOT choose to rollback the statement you're
executing...it just does so if it fails.

As to whether any of that behaviour is strange... That depends upon your
viewpoints and experience, so I could understand that. The situation
remains....it IS the behaviour and my understanding is that this was the
behaviour we were seeking to emulate?

My confusion with the SAVEPOINT/NESTED debate is - how do you know
whether you SHOULD HAVE issued a SUBBEGIN? When I issue a SAVEPOINT, I
don't care whether or not I've issued a SUBBEGIN before, I just do it
and it works.

The only way to do this seems to be to avoid having a flat nesting
structure, but to have an infinite descent on one part of the nesting
tree...so each statement IMPLICTLY starts with a SUBBEGIN, and SAVEPOINT
and COMMIT just count 'em so they know how many SUBCOMMITs to issue to
get back up again.

e.g.
BEGIN
(SUBBEGIN)
INSERT...
(SUBBEGIN)
INSERT....
(SUBBEGIN)
INSERT...
SAVEPOINT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT)
(SUBBEGIN)
INSERT...
(SUBBEGIN)
INSERT....
(SUBBEGIN)
INSERT...
COMMIT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT, COMMIT)

Best Regards, Simon Riggs