From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bulgakovalexey1980@gmail.com
Subject: BUG #17050: cursor with for update + commit in loop
Date: Tue, 08 Jun 2021 07:49:25 +0000
Message-ID: <17050-f77aa827dc85247c@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bulgakovalexey1980@gmail.com
Subject: Re: BUG #17050: cursor with for update + commit in loop
In-Reply-To: <17050-f77aa827dc85247c@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox