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

Поиск
Список
Период
Сортировка
Искать
От
PG Bug reporting form
Тема
BUG #17050: cursor with for update + commit in loop
Дата
Msg-id
17050-f77aa827dc85247c@postgresql.org
Список
Дерево обсуждения
BUG #17050: cursor with for update + commit in loop PG Bug reporting form <noreply@postgresql.org>
Re: BUG #17050: cursor with for update + commit in loop Tom Lane <tgl@sss.pgh.pa.us>
Re: BUG #17050: cursor with for update + commit in loop Tom Lane <tgl@sss.pgh.pa.us>
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

В списке pgsql-bugs по дате отправления
От: Noah Misch
Дата:
От: 甄明洋
Дата:
FAQ