Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

Comments in-line.

On Tue, 2022-10-18 at 21:02 -0700, Christophe Pettus wrote:
>
>
> > On Oct 18, 2022, at 19:18, gogala(dot)mladen(at)gmail(dot)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.

That depends. Multiple commits will slow down the processing. If the
goal of the exercise is to let update complete as quickly as possible,
then it will be a single commit. If the goal is to enable normal
processing and let the enormous update complete in its due time, then
you'll do what you describe.

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

Point of my post is that the business logic, in your case it's IoT
sensors, determines what is transaction and when to commit. Advice like
"commit often and commit early", to paraphrase the famous Chicago
mayor, is easy to find but I would take it with grain of salt.
Regards
--
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com