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