BUG #17050: cursor with for update + commit in loop

The Post Office <noreply@postgresql.org>

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bulgakovalexey1980@gmail.com
Date: 2021-06-08T07:49:25Z
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      17050
Logged by:          Алексей Булгаков
Email address:      bulgakovalexey1980@gmail.com
PostgreSQL version: 12.7
Operating system:   Red Hat 4.4.7-23
Description:        

create table public.test_tuple_stream (
	   id serial,
	   nm text,
	   dt timestamptz,
	   num bigint
	   );

CREATE OR REPLACE PROCEDURE public.test_tuple_stream()
 LANGUAGE plpgsql
AS $procedure$
  declare
	  l_cur cursor for
	    select id
	    from public.test_tuple_stream
	    order by id
	    for update;
	begin
	  for rec in l_cur loop
	    update public.test_tuple_stream
	    set num = num + 1
	    where id = rec.id;
	   
	    commit;	   
	  end loop;
	 
	  commit;
	END;
$proc	  
	  
--	truncate table public.test_tuple_stream;  
	  
	insert into public.test_tuple_stream(nm, dt, num)
	values ('A', now(), 1);
	insert into public.test_tuple_stream(nm, dt, num)
	values ('B', now(), 1);
	insert into public.test_tuple_stream(nm, dt, num)
	values ('C', now(), 1);

  call public.test_tuple_stream()

	select *
	from public.test_tuple_stream
	order by id

If run procedure test_tuple_stream then in result updated 2 rows of 3.
Why?
if remove in procedure "for update" or "commit in loop" then updated 3 rows
of 3

Commits

  1. Force NO SCROLL for plpgsql's implicit cursors.

  2. Avoid misbehavior when persisting a non-stable cursor.