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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: bulgakovalexey1980(at)gmail(dot)com
Subject: BUG #17050: cursor with for update + commit in loop
Date: 2021-06-08 07:49:25
Message-ID: 17050-f77aa827dc85247c@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17050
Logged by: Алексей Булгаков
Email address: bulgakovalexey1980(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message 甄明洋 2021-06-08 08:17:09 setting the timezone parameter with space cause diff result
Previous Message Noah Misch 2021-06-08 05:31:55 Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch