Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

> On Oct 18, 2022, at 19:18, gogala.mladen@gmail.com wrote:
>
> Commit within a loop is an extremely bad idea.

This is an over-generalization.  There are many use-cases for this (if there were not, procedures wouldn't have been
nearlyas important a feature). 

For example, if you are processing a large update (in the hundreds of thousands or more of rows), you often want to
commitregularly so that other processes don't have to wait for the whole thing to finish due to row-level locks, and to
givevacuum a chance to deal with the dead tuples.  Similarly, while inserting one row at a time and committing is
usuallynot a great idea, it can make sense to do large inserts in batches. 

Applications do this kind of thing all the time, very successfully; it was just that the loop was in the application
ratherthan in the procedure. 

High commit rates happen all the time, and they don't break PostgreSQL.  For example, an IoT application collecting
sensordata and doing many inserts per second is also doing many commits per second, since each bare INSERT is in its
owntransaction.  PostgreSQL handles it just fine.