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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17050: cursor with for update + commit in loop
Дата
Msg-id 17050-f77aa827dc85247c@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17050: cursor with for update + commit in loop
Список 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


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Noah Misch
Дата:
Сообщение: Re: BUG #16961: Could not access status of transaction
Следующее
От: 甄明洋
Дата:
Сообщение: setting the timezone parameter with space cause diff result