CODEWITHSUNDEEP
postgresqlcommitsql-delete
Stuart
Stuart

Reputation: 1

Finding COMMIT impossible in a Postgres Housekeeping Procedure

I have written a Housekeeping procedure in Postgres, with the simple logic...

DECLARE BEGIN LOOP (while there's data to process) DELETE 1000 rows (configurable size) COMMIT END LOOP END; The problem is, the error I get is;

ERROR: invalid transaction termination Where: PL/pgSQL function test.hskp(character varying,numeric,numeric) line 99 at COMMIT

Without the COMMIT it works fine.

is it just impossible to do this sort of BATCH approach, or is there a simple workaround?

Does anyone have an example?

Upvotes: 0

Views: 126

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

You cannot COMMIT inside a PostgreSQL function. You could use a procedure instead, but the easiest and recommended way is not to delete rows in batches, but all at once.

Upvotes: 0

Related Questions