In PostgreSQL (I use 14 and 16) I am trying to create a function that inserts a record into a table that has two columns: an identifier (integer) and a string "Some string". I want to write a loop to insert N records, but commit after every K inserts so that I can view what is going on from another session. How do I do this? So far, I have tried many options with all being some variant of this:
DROP TABLE IF EXISTS testtable;
CREATE TABLE IF NOT EXISTS testtable
(
identifier bigint NOT NULL,
description character varying(128),
CONSTRAINT testtable_pkey PRIMARY KEY (identifier)
);
CREATE OR REPLACE FUNCTION insert_records(N INT, K INT)
RETURNS VOID AS $$
DECLARE
i INT;
BEGIN
FOR i IN 1..N LOOP
INSERT INTO testtable (identifier, description) VALUES (i, 'some string');
IF MOD(i, K) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END $$ LANGUAGE plpgsql;
SELECT insert_records(10, 2);
SELECT * FROM testtable;
But I get this error for the above code, and others for every variant I have tried (e.g., commit followed by begin inside the IF condition, etc.
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function insert_records(integer,integer) line 9 at COMMIT
How do we commit after every K-th insert? Should this function be written a different way? Thanks.
2
Answers
Using a procedure instead of a function solved the problem. Pasting it here to help others:
As PostgreSQL functions operate within the context of the surrounding transaction block, it is not permitted to utilize COMMIT inside a PL/pgSQL function. To get a comparable result, you should instead utilize SAVEPOINT and ROLLBACK TO.
Try this updated code. I used SAVEPOINT in this function to identify transactional points that you can later roll back to. At the end, it rolls back to the last savepoint, thereby committing the last few changes, and commits all K inserts. In this way, you can accomplish the intended behavior of committing following each K-th insert.
Here is the code;
Hope it’s helpful 🙂