Andreas Pflug wrote:
Simon Riggs wrote:
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)
No, SAVEPOINT is not some kind of intermediate commit, but a point
where a rollback can rollback to.
Look at this oracle stuff when googling for SAVEPOINT ROLLBACK:
BEGIN
SAVEPOINT before_insert_programmers;
insert_programmers (p_deptno);
EXCEPTION
WHEN OTHERS THEN ROLLBACK TO before_insert_programmers;
END;
There's no need for an intermediate commit, because the top level
rollback would overrule it (if not, it would be an independent
transaction, not nested).
I'd opt for BEGIN as a start of a subtransaction (no need for special
semantics in plpgsql), the corresponding END simply changes the
transaction context to the parent level.
BEGIN is an unnamed savepoint in this case, so if we have SAVEPOINT
<name> we'd also have the corresponding ROLLBACK TO [SAVEPOINT]
<name>. For the unnamed savepoint ROLLBACK INNER or ROLLBACK SUB could
be used.
This would be an extension to oracle's usage, which seems quite
reasonable to me.
What happens when you use subtransactions? I think there might be a
visibility issue and how far do you unwind the depth of subtransactions
or transactions?
BEGIN
UPDATE A
SAVEPOINT X
BEGIN
BEGIN
UPDATE B
BEGIN
UPDATE C
ROLLBACK TO SAVEPOINT X
COMMIT
COMMIT
COMMIT
COMMIT
Or
SAVEPOINT X
BEGIN
UPDATE A
ROLLBACK TO SAVEPOINT X
COMMIT
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
