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:
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.
