Re: Unexpected results from CALL and AUTOCOMMIT=off

[Pierre Forstmann <pierre.forstmann@xxxxxxxxx>]

You declared function f_get_x as stable which means:

https://www.postgresql.org/docs/15/sql-createfunction.html

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

If you remove stable from function declaration, it works as expected:

drop table t_test;
DROP TABLE
create table t_test(x bigint);
CREATE TABLE
insert into t_test values(0);
INSERT 0 1
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 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$;
CREATE PROCEDURE
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; $$;
psql:test.sql:38: NOTICE:  do >> x=1
psql:test.sql:38: NOTICE:  f_print_x() >> x=1
DO

Le lun. 3 juin 2024 à 16:42, Victor Yegorov <vyegorov@xxxxxxxxx> a écrit :

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