MIME-Version: 1.0
From: Victor Yegorov <vyegorov@gmail.com>
Date: Mon, 3 Jun 2024 17:41:38 +0300
Message-ID: <CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com>
Subject: Unexpected results from CALL and AUTOCOMMIT=off
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006645a50619fd5532"
Archived-At: <https://www.postgresql.org/message-id/CAGnEboiRe%2BfG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej%2BE4%3DVw%40mail.gmail.com>
Precedence: bulk

--0000000000006645a50619fd5532
Content-Type: text/plain; charset="UTF-8"

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

--0000000000006645a50619fd5532
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Greetings.<br><br>I am observing the following results on =
PostgreSQL 15.7<br>First, setup:<br><br>create table t_test(x bigint);<br>i=
nsert into t_test values(0);<br><br>create or replace function f_get_x()<br=
>returns bigint<br>language plpgsql<br>stable<br>as $function$<br>declare<b=
r>=C2=A0 =C2=A0 l_result bigint;<br>begin<br>=C2=A0 =C2=A0 select x into l_=
result from t_test;<br>=C2=A0 =C2=A0 --raise notice &#39;f_get_x() &gt;&gt;=
 x=3D%&#39;, l_result;<br>=C2=A0 =C2=A0 --raise notice &#39;f_get_x() &gt;&=
gt; xact=3D%&#39;, txid_current_if_assigned();<br>=C2=A0 =C2=A0 return l_re=
sult;<br>end;<br>$function$;<br><br>create or replace procedure f_print_x(x=
 bigint)<br>language plpgsql<br>as $procedure$<br>begin<br>=C2=A0 =C2=A0 ra=
ise notice &#39;f_print_x() &gt;&gt; x=3D%&#39;, x;<br>=C2=A0 =C2=A0 --rais=
e notice &#39;f_print_x() &gt;&gt; xact=3D%&#39;, txid_current_if_assigned(=
);<br>end;<br>$procedure$;<br><br><br>Now, the case:<br>\set AUTOCOMMIT off=
<br>do<br>$$ begin<br>=C2=A0 =C2=A0 --raise notice &#39;do &gt;&gt; xact=3D=
%&#39;, txid_current_if_assigned();<br>=C2=A0 =C2=A0 update t_test set x =
=3D 1;<br>=C2=A0 =C2=A0 --raise notice &#39;do &gt;&gt; xact=3D%&#39;, txid=
_current_if_assigned();<br>=C2=A0 =C2=A0 raise notice &#39;do &gt;&gt; x=3D=
%&#39;, f_get_x();<br>=C2=A0 =C2=A0 --raise notice &#39;do &gt;&gt; xact=3D=
%&#39;, txid_current_if_assigned();<br>=C2=A0 =C2=A0 call f_print_x(f_get_x=
());<br>end; $$;<br><div>NOTICE: =C2=A0do &gt;&gt; x=3D1<br>NOTICE: =C2=A0f=
_print_x() &gt;&gt; x=3D0<br>DO<br><br>I don&#39;t understand why CALL stat=
ement is not seeing an updated record.<br>With AUTOCOMMIT=3Don, all goes as=
 expected.<br><br>I tried to examine snapshots and xids (commented lines), =
but they&#39;re always the same.<br><br>Can you explain this behavior, plea=
se? Is it expected?<br><br></div><span class=3D"gmail_signature_prefix">-- =
</span><br><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gma=
il_signature"><div dir=3D"ltr"><div>Victor Yegorov</div></div></div></div>

--0000000000006645a50619fd5532--