From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
Cc: | Christophe Pettus <xof(at)thebuild(dot)com>, Ravi Krishna <s_ravikrishna(at)aol(dot)com>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP |
Date: | 2022-10-18 21:55:28 |
Message-ID: | 811cd41fda48a12fe2c430e03dbf0f7fd57d49d2.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 2022-10-18 at 17:33 -0400, Tom Lane wrote:
> Bryn Llewellyn <bryn(at)yugabyte(dot)com> writes:
> > xof(at)thebuild(dot)com 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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2022-10-18 21:56:35 | Re: what's inherited |
Previous Message | Ted Toth | 2022-10-18 21:41:43 | what's inherited |