Question 234869 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – cursor for loop without 'on hold' but with commit inside

The documentation on cursors states:

If neither WITHOUT HOLD nor WITH HOLD is specified, WITHOUT HOLD is the default […] If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session

In my below example I do not specify WITH HOLD and still, the anyonymous block successfully copies all 1000 rows from table a to table b:

create table a (c) as SELECT * FROM generate_series(1, 1000);

create table b as select * from a limit 0;

do $$
DECLARE
    _rec RECORD;
BEGIN
    FOR _rec IN 
        select c from a   
    LOOP
        insert into b values (_rec.c);
        commit;
    END LOOP;
END;
$$

select * from b;
-- exactly like table a 

select count(*) from b;
-- 1000

Why does the example work in the way it does? It seems it behaves like if WITH HOLD was set? So is what happening here the same as if WITH HOLD was set, which is that upon the first commit a snapshot of the results of the cursor is made and that snapshot is used for the remainder of the loops? Or is something else going on that allows the cursor to see the data as it was when the anonymous block was started? Or does it see new data after each commit (can’t believe that is the case)?

Tags:

2

Answers


  1. 0

    You should specifically declare the cursor with WITH HOLD in order to get the desired behavior of a cursor that maintains its location between commits. Try this code, I have added the functionality into it.

    CREATE TABLE a (c) AS SELECT * FROM generate_series(1, 1000);

CREATE TABLE b AS SELECT * FROM a LIMIT 0;

DO $$
DECLARE
    _rec RECORD;
    my_cursor CURSOR WITH HOLD FOR
        SELECT c FROM a;
BEGIN
    OPEN my_cursor;
    LOOP
        FETCH NEXT FROM my_cursor INTO _rec;
        EXIT WHEN NOT FOUND;
        
        INSERT INTO b VALUES (_rec.c);
        COMMIT; -- Commit inside the loop
    END LOOP;
    CLOSE my_cursor;
END;
$$;

SELECT * FROM b;

    Hope it solves your issue 🙂

    Login or Signup to reply.
  2. 0

    Your link is for DECLARE, which you never use (in the cursor sense) in your code. You are using automatic plpgsql cursors, which are obviously related to but not identical to SQL cursors. From the relevant doc for that:

    Normally, cursors are automatically closed at transaction commit. However, a cursor created as part of a loop like this is automatically converted to a holdable cursor by the first COMMIT or ROLLBACK. That means that the cursor is fully evaluated at the first COMMIT or ROLLBACK rather than row by row. The cursor is still removed automatically after the loop, so this is mostly invisible to the user.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search