COMMIT in PostgreSQL stored procedure

Question

Hi,

I have a PostgreSQL stored procedure which loops over a very large list, and makes changes to some of its members using UPDATE.

Is there a way to commit these changes per iteration, not at the end of the function execution? It would allow me to run the function for shorts periods of time, making small changes at each run.

Thanks,

Adam

Answer 1

No, it's currently not supported to open or close transactions inside a stored procedure, no.

If it did, btw, committing after each iteration would make things a lot slower. You'd have to at least commit in batches of 10,000 or 100,000 updates. And as was said in the comments, the real win is of course not to run this ISAM style but to figure out some way to write it as a single query.