On Tue, 2022-10-18 at 17:33 -0400, Tom Lane 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:
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:
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