On 10/19/22 08:06, Mladen Gogala wrote:
[snip]
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.
In the normal course of operation (i.e, not when bulk loading), you
should commit at the end of every "business transaction". We've committed after X business when running stovepipe "batch" jobs processing input files. In those cases, though, we had to track progress through the file; in the case of a rollback, the application had to go back to the last input file "save point" and start over.
--
Angular momentum makes the world go 'round.