Cursor retrieves a deleted row in table created within plpgsql function

Question

Within a plpgsql function I create a table and use a cursor to access its rows. While at first row, I delete a following one and surprisingly (to me at least) the cursor fetches it. When repeating within the same function, it works as I expected it so.

However, if the table pre-exists and is not created within the function, the deleted row is never fetched.

What am I missing?

DECLARE
curs1 refcursor;
rec record;

BEGIN
CREATE TABLE test as select generate_series(1,5,1) test_id;

OPEN curs1 FOR SELECT * FROM test ORDER BY test_id; 
  LOOP
    FETCH curs1 INTO rec;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE 'ROW:%',rec.test_id;
    IF rec.test_id=1 THEN
      DELETE FROM TEST WHERE test_id=3;
    END IF;
  END LOOP;
CLOSE curs1;

RAISE NOTICE 'AGAIN';
--just repeating without deleting

OPEN curs1 FOR SELECT * FROM test ORDER BY test_id; 
  LOOP
    FETCH curs1 INTO rec;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE 'ROW:%',rec.test_id;
  END LOOP;
CLOSE curs1;

Output is:

NOTICE:  ROW:1
NOTICE:  ROW:2
NOTICE:  ROW:3
NOTICE:  ROW:4
NOTICE:  ROW:5
NOTICE:  AGAIN
NOTICE:  ROW:1
NOTICE:  ROW:2
NOTICE:  ROW:4
NOTICE:  ROW:5

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2015-09-01T02:23:40

The reason is that Postgres cursors are "insensitive" by default. The documentation:

The SQL standard says that it is implementation-dependent whether cursors are sensitive to concurrent updates of the underlying data by default. In PostgreSQL, cursors are insensitive by default, and can be made sensitive by specifying FOR UPDATE. Other products may work differently.

Bold emphasis mine.

So try the same with using the FOR UPDATE clause:

DO
$$
DECLARE
   curs1 refcursor;
   rec record;
BEGIN
   CREATE TABLE test AS SELECT generate_series(1,5) test_id;

   OPEN curs1 FOR SELECT * FROM test ORDER BY test_id FOR UPDATE; 
   DELETE FROM test WHERE test_id = 3;
   LOOP
      FETCH curs1 INTO rec;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE 'ROW:%',rec.test_id;
   END LOOP;
   CLOSE curs1;
END
$$

And you get:

NOTICE:  ROW:1
NOTICE:  ROW:2
NOTICE:  ROW:4
NOTICE:  ROW:5

Row 3 is not visible any more.

Cursor retrieves a deleted row in table created within plpgsql function

1 Answers