public inbox for [email protected]  

help / color / mirror / Atom feed

From: Victor Yegorov <[email protected]>
To: pgsql-general <[email protected]>
Subject: Unexpected results from CALL and AUTOCOMMIT=off
Date: Mon, 3 Jun 2024 17:41:38 +0300
Message-ID: <CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Unexpected results from CALL and AUTOCOMMIT=off
  In-Reply-To: <CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox