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
-
Force NO SCROLL for plpgsql's implicit cursors.
- c5b28184132d 13.4 landed
- be9009890747 14.0 landed
- 5b7bf9f72ab5 11.13 landed
- 182323300620 12.8 landed
-
Avoid misbehavior when persisting a non-stable cursor.
- ba2c6d6cec00 14.0 landed
- c3b5082685dd 12.8 landed
- c1fd756fd23f 13.4 landed
- 2757865fa7fb 11.13 landed