On Tue, 2022-10-18 at 17:33 -0400, Tom Lane wrote: > Bryn Llewellyn <bryn@xxxxxxxxxxxx> writes: > > xof@xxxxxxxxxxxx wrote: > > > You can commit in a loop, but not in BEGIN / END block that has an exception handler: that creates a subtransaction for the duration of the BEGIN / END. > > > This surprised me when I first started to use PG (after all those years > > with ORCL). > > Really? BEGIN with an exception block is a subtransaction because it's > defined to roll back to the database state as of the start of the block > if an exception occurs. COMMIT in the middle fundamentally conflicts > with that, I should think. Does Oracle interpret that differently? Looks like Oracle doesn't care much about that: SQL> CREATE TABLE mytab (id integer CHECK (id > 0)); Table created. SQL> CREATE PROCEDURE committest IS 2 BEGIN 3 INSERT INTO mytab VALUES (42); 4 COMMIT; 5 INSERT INTO mytab VALUES (-42); 6 EXCEPTION 7 WHEN OTHERS THEN 8 NULL; 9 END; 10 / Procedure created. SQL> CALL committest(); Call completed. SQL> SELECT * FROM mytab; ID ---------- 42 I looks like Oracle allows you to randomly interfere with its transaction handling. If you run commit and then enter an exception handler, it simply doesn't rollback. Yours, Laurenz Albe
