Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

On Tue, 2022-10-18 at 14:31 -0700, Christophe Pettus wrote:
>
> Rather than have a loop inside the BEGIN / END, you could put the
> BEGIN EXCEPTION END inside the loop, catch the error, store the
> important parts of the exception in a variable, and then do the
> COMMIT after the END statement but before the next iteration of the
> loop.  A bit messier, but it gets the job done.

Commit within a loop is an extremely bad idea. Commit is an expensive
operation which includes incrementing the XID, which is global and not
local to the process. There is also a WAL write which has to be waited
on. Every commit implies at least one write operation. If that was not
the case, Postgres wouldn't be ACID compliant. There would be problem
with the "D". Commit within a loop will have many adverse effects on
the performance. Here is what Tom Kyte, who used to be a celebrity in
the world of Oracle, said on this topic:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:4951966319022

Surprisingly enough, the argument in the article is mostly portable,
doesn't depend on the database type at all. Basically, transaction is a
logical unit of work. If 1000 rows need to be updated, it's better to
update them in a single transaction than in 1000 transactions. That is
particularly true for Postgres which doesn't have problems with the
undo tablespace and ORA-1555 "snapshot too old" error because of the
different architecture. Also, Oracle has a secret optimization: it
doesn't wait for commit, if the commit is issued within PL/SQL loop.
Your idea solves the syntactic problem with commits within PLPG/SQL
 loops but it doesn't solve other problems that such programming
causes. Commit within loop is a very bad idea. The best way to resolve
the problems with commit within the loop is to remove the programmer
trying to do that from the project. In my humble opinion, programmers
who do stuff like that should suffer unusual and cruel punishment.

PS:
----
I am sure, that if Tom Kyte would dare to make a suggestion on this
list, there would be someone who would try to explain "the Postgres
way" to him in a condescending manner. I've seen that before.