Unexpected results from CALL and AUTOCOMMIT=off

Поиск
Список
Период
Сортировка
От Victor Yegorov
Тема Unexpected results from CALL and AUTOCOMMIT=off
Дата
Msg-id CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Unexpected results from CALL and AUTOCOMMIT=off
Список pgsql-general
Greetings.

I am observing the following results on PostgreSQL 15.7
First, setup:

create table t_test(x bigint);
insert into t_test values(0);

create or replace function f_get_x()
returns bigint
language plpgsql
stable
as $function$
declare
    l_result bigint;
begin
    select x into l_result from t_test;
    --raise notice 'f_get_x() >> x=%', l_result;
    --raise notice 'f_get_x() >> xact=%', txid_current_if_assigned();
    return l_result;
end;
$function$;

create or replace procedure f_print_x(x bigint)
language plpgsql
as $procedure$
begin
    raise notice 'f_print_x() >> x=%', x;
    --raise notice 'f_print_x() >> xact=%', txid_current_if_assigned();
end;
$procedure$;


Now, the case:
\set AUTOCOMMIT off
do
$$ begin
    --raise notice 'do >> xact=%', txid_current_if_assigned();
    update t_test set x = 1;
    --raise notice 'do >> xact=%', txid_current_if_assigned();
    raise notice 'do >> x=%', f_get_x();
    --raise notice 'do >> xact=%', txid_current_if_assigned();
    call f_print_x(f_get_x());
end; $$;
NOTICE:  do >> x=1
NOTICE:  f_print_x() >> x=0
DO

I don't understand why CALL statement is not seeing an updated record.
With AUTOCOMMIT=on, all goes as expected.

I tried to examine snapshots and xids (commented lines), but they're always the same.

Can you explain this behavior, please? Is it expected?

--
Victor Yegorov

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

Предыдущее
От: Ron Johnson
Дата:
Сообщение: Re: Postgresql 16.3 Out Of Memory
Следующее
От: Pierre Forstmann
Дата:
Сообщение: Re: Unexpected results from CALL and AUTOCOMMIT=off