skip to Main Content

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


  1. Chosen as BEST ANSWER

    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;
    $$;
    

  2. 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 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search