> On Oct 18, 2022, at 19:18, gogala.mla...@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 nearly as 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 commit regularly so that other processes
don't have to wait for the whole thing to finish due to row-level locks, and to
give vacuum a chance to deal with the dead tuples. Similarly, while inserting
one row at a time and committing is usually not 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 rather than in the procedure.
High commit rates happen all the time, and they don't break PostgreSQL. For
example, an IoT application collecting sensor data and doing many inserts per
second is also doing many commits per second, since each bare INSERT is in its
own transaction. PostgreSQL handles it just fine.
