Re: Unexpected results from CALL and AUTOCOMMIT=off

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(at)gmail(dot)com> 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
>