Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

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?
>
>                         regards, tom lane
>
>

Hi Tom,
Yes, Oracle does interpret that differently. Bryn may correct me if I'm
wrong, but Oracle creates an implicit save point when it encounters
BEGIN. Exception handler doesn't necessarily roll things back. Oracle
behavior is not standard and PgSQL adheres to SQL standard better than
Oracle. However, being as pervasive as it is, Oracle is de facto
 standard.
Also, Oracle has something called "autonomous transaction" which, in
effect, means that a session can have two concurrent transactions open,
which is also non-standard:

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/AUTONOMOUS_TRANSACTION-pragma.html#GUID-AD33D949-081B-4CD3-A240-C29773E908C3

Amazon, lead by Kevin Closson, the guy who has famously designed Oracle
Exadata among other things, even came up with the recipe how to migrate
it to Postgres:

https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-to-postgresql/

I am a bit skeptical toward that recipe and I usually prefer
programming solutions with opening another thread and sending a
message. BTW, speaking of Kevin, he has also written pgio, which is a
PostgreSQL version of his SLOB package. Kevin is the only retired
Oracle ACE in existence. BTW, Bryn also used to be an Oracle ACE.
Regards