Committing after every k insertions in PostgreSQL

Question

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.

Answer 1

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;

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 every K inserts
      COMMIT;
      -- Create a new savepoint
      SAVEPOINT savepoint_insert;
    END IF;
  END LOOP;

  -- Rollback to the last savepoint to keep the changes made after the last commit
  ROLLBACK TO SAVEPOINT savepoint_insert;
  -- Commit the remaining changes
  COMMIT;
END $$ LANGUAGE plpgsql;

-- Call the function
SELECT insert_records(10, 2);

-- Check the results
SELECT * FROM testtable;

Hope it's helpful :)

Answer 2

Using a procedure instead of a function solved the problem. Pasting it here to help others:

drop procedure if exists insert_n_rows_proc;
CREATE OR REPLACE PROCEDURE insert_n_rows_proc(n integer, k integer)
LANGUAGE plpgsql
AS $$
DECLARE
    i integer := 0;
BEGIN
    WHILE i < n LOOP
        INSERT INTO testtable VALUES (i, 'Some string');
        i := i + 1;
        IF i % k = 0 THEN
            COMMIT;
            -- Print out a message to the console to indicate that a commit has happened
            RAISE NOTICE 'Committing after inserting % rows', i;
        END IF;
    END LOOP;
END;
$$;