COMMIT in PostgreSQL stored procedure

Question

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

The real performance optimization is to use a single `update` statement rather than looping over the whole list in a `cursor` or via some other method. Is this not possible in your scenario? — yfeldblum, Feb 21 '10 at 16:46
Starting with PostgreSQL 11, a [PROCEDURE](https://www.postgresql.org/docs/current/sql-createprocedure.html) can be used instead of a FUNCTION, and procedures are allowed to commit. — Daniel Vérité, Jan 08 '19 at 14:43

score 3 · Accepted Answer · answered Feb 22 '10 at 07:23

3

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.

answered Feb 22 '10 at 07:23

Magnus Hagander

23,890
5
56
43

Thanks a lot, I'll probably change my approach. – Adam Matan Feb 22 '10 at 07:55

score 1 · Answer 2 · answered Jul 18 '12 at 15:31

There is a cost per statement, so if you can write your function to do less statements, you're better off...

FOR all IN (select * from TABLE1)
LOOP
    FOR some IN (select * from)
    LOOP
        INSERT INTO TABLE2 VALUES (all.id, some.id)
    END LOOP
END LOOP

Replace the whole loop with a single INSERT statement:

INSERT INTO TABLE2 SELECT all.id, some.id FROM all, some WHERE...

But beware of the size of the list you are going to update. We had a similar problem, we have to create a lot of tables dynamically and insert a lot of data in them. Firstly we create a stored procedure and loops over a list of months and years and create a single table for every month, but it collapse in a single stored procedure. So, we create the stored procedure but we don't loops in there, instead we loops outside the stored procedure and it works.

COMMIT in PostgreSQL stored procedure

2 Answers2

Linked