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
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 |