Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sEBfm-0007sr-FL for pgsql-general@arkaria.postgresql.org; Mon, 03 Jun 2024 17:40:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sEBfm-00BqGR-Bt for pgsql-general@arkaria.postgresql.org; Mon, 03 Jun 2024 17:40:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sEBfl-00BqGJ-Vc for pgsql-general@lists.postgresql.org; Mon, 03 Jun 2024 17:40:42 +0000 Received: from mail-lf1-x136.google.com ([2a00:1450:4864:20::136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sEBfi-002A48-JL for pgsql-general@postgresql.org; Mon, 03 Jun 2024 17:40:41 +0000 Received: by mail-lf1-x136.google.com with SMTP id 2adb3069b0e04-52b9af7a01bso1584519e87.0 for ; Mon, 03 Jun 2024 10:40:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1717436436; x=1718041236; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=IhCKZDsSHRzSL5t+Z9XC8fnMFCS0wF+rJ0RNXnVyFYQ=; b=nbmbXazSob9N+Alh4ubLTLiuHIMobOUF8AVDhsXSlRXAFwvT+9zhzHobFYA55yTrnr eXv4o5QXpKoK6HXqj2X4DKjpxyRt+yb06W2ACtwMSewnzPIsPX9qVMXME549hGO/JDMA 8axDcOj1xDpk+CVIiwXSKIRttwkGMkYDmR4cqjcsNHotERRCnrFy48hLZopZYALyYIO8 aQe71I6tzUteGVydMpsjt+6LlGo8KeOh0h9Xh89tZOcXcd8mhhwVxnvU/rAiYAJjQhiR Zt0qzx/A7EOk24YcBUtuelLugOVjjTKQDJvXKWGbfSClezOxikQ0Ji2UUrs9mjx/XIQv CLTQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717436436; x=1718041236; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=IhCKZDsSHRzSL5t+Z9XC8fnMFCS0wF+rJ0RNXnVyFYQ=; b=BNUlYBEKofWAqjvTdYRHmI9IAsIq6ofS7qg1olBZiBnDXnJ98BSeqVnzArqvUNJ6Ss eUeqQjXGYohHzt0D6shUOeedu6QUiQE2sM6dieq5dmvBmyA7OGBvm6MqF5qMwV38dP3w ya7gRToLGMhZrWqTOQb+J9qsW1kdjurF6PsFutt91zklDnLhxIkfX9BBScLlcyhjq2Fq FodePDCZdh/VgyZRCtG3zmPhfjKJ9eiMeBc/9x95jpoY5JMhDLhAxyR1A/Sir6E/U8pA djj81iIN9cMsFIjWLtifygUSw+QPUYUnTQzPGaUFztvCvCDRLtuFFyp8QxxQZP/fL5zE kA6w== X-Gm-Message-State: AOJu0Yz/ppd70Ko+lDRmXBSRbmmxxUj0RvsH3rqwhnKneHtPPH04wgWx +3zm7uwWbk5+ToKHPyTswJkfp+RNlMoqcJ1YWLcQEnqFr5CRLUN2NZBN7Ch2fnF2VZ5ZdZJeA10 nbr7oz2vJmKzfe8sftFcYvAgUmQw= X-Google-Smtp-Source: AGHT+IFjEp8H7SMRMXTUGJ+D8iW8sDVe6GaTNhQgpjtmJykhwMS2cP2pBYXQOJDcQ47ccttdE2MeeXHM2gK9Cm2H6Kg= X-Received: by 2002:ac2:5b8c:0:b0:52b:3738:f56b with SMTP id 2adb3069b0e04-52b895970ecmr7111343e87.36.1717436436162; Mon, 03 Jun 2024 10:40:36 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pierre Forstmann Date: Mon, 3 Jun 2024 19:40:24 +0200 Message-ID: Subject: Re: Unexpected results from CALL and AUTOCOMMIT=off To: Victor Yegorov Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000b3b3200619ffd45e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b3b3200619ffd45e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=3D%', l_result; --raise notice 'f_get_x() >> xact=3D%', 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=3D%', x; --raise notice 'f_print_x() >> xact=3D%', txid_current_if_assigned(); end; $procedure$; CREATE PROCEDURE do $$ begin --raise notice 'do >> xact=3D%', txid_current_if_assigned(); update t_test set x =3D 1; --raise notice 'do >> xact=3D%', txid_current_if_assigned(); raise notice 'do >> x=3D%', f_get_x(); --raise notice 'do >> xact=3D%', txid_current_if_assigned(); call f_print_x(f_get_x()); end; $$; psql:test.sql:38: NOTICE: do >> x=3D1 psql:test.sql:38: NOTICE: f_print_x() >> x=3D1 DO Le lun. 3 juin 2024 =C3=A0 16:42, Victor Yegorov a =C3= =A9crit : > 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=3D%', l_result; > --raise notice 'f_get_x() >> xact=3D%', 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=3D%', x; > --raise notice 'f_print_x() >> xact=3D%', txid_current_if_assigned(); > end; > $procedure$; > > > Now, the case: > \set AUTOCOMMIT off > do > $$ begin > --raise notice 'do >> xact=3D%', txid_current_if_assigned(); > update t_test set x =3D 1; > --raise notice 'do >> xact=3D%', txid_current_if_assigned(); > raise notice 'do >> x=3D%', f_get_x(); > --raise notice 'do >> xact=3D%', txid_current_if_assigned(); > call f_print_x(f_get_x()); > end; $$; > NOTICE: do >> x=3D1 > NOTICE: f_print_x() >> x=3D0 > DO > > I don't understand why CALL statement is not seeing an updated record. > With AUTOCOMMIT=3Don, 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 > --000000000000b3b3200619ffd45e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You declared function f_get_x as stable which means:<= /div>


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

If you remove stable fro= m function declaration, it works as expected:

drop= table t_test;
DROP TABLE
create table t_test(x bigint);
CREATE TA= BLE
insert into t_test values(0);
INSERT 0 1=
create or replace function f_get_x()
returns bigint
language plpg= sql
-- stable
as $function$
declar= e
=C2=A0 =C2=A0 l_result bigint;
begin
=C2=A0 =C2=A0 select x into= l_result from t_test;
=C2=A0 =C2=A0 --raise notice 'f_get_x() >&= gt; x=3D%', l_result;
=C2=A0 =C2=A0 --raise notice 'f_get_x() &g= t;> xact=3D%', txid_current_if_assigned();
=C2=A0 =C2=A0 return l= _result;
end;
$function$;
CREATE FUNCTION
create or replace procedure f_print_x(x bigint)
language plpg= sql
as $procedure$
begin
=C2=A0 =C2=A0 raise notice 'f_print_x= () >> x=3D%', x;
=C2=A0 =C2=A0 --raise notice 'f_print_x()= >> xact=3D%', txid_current_if_assigned();
end;
$procedure$= ;
CREATE PROCEDURE
do
$$ begin
= =C2=A0 =C2=A0 --raise notice 'do >> xact=3D%', txid_current_i= f_assigned();
=C2=A0 =C2=A0 update t_test set x =3D 1;
=C2=A0 =C2=A0 = --raise notice 'do >> xact=3D%', txid_current_if_assigned();<= br>=C2=A0 =C2=A0 raise notice 'do >> x=3D%', f_get_x();
= =C2=A0 =C2=A0 --raise notice 'do >> xact=3D%', txid_current_i= f_assigned();
=C2=A0 =C2=A0 call f_print_x(f_get_x());
end; $$;
psql:test.sql:38: NOTICE: =C2=A0do >> x=3D1
psql:test.sql:38:= NOTICE: =C2=A0f_print_x() >> x=3D1
DO

Le=C2=A0lun. 3 juin 2024 = =C3=A0=C2=A016:42, Victor Yegorov <vyegorov@gmail.com> a =C3=A9crit=C2=A0:
Greetings.

I am ob= serving the following results on PostgreSQL 15.7
First, setup:

cr= eate table t_test(x bigint);
insert into t_test values(0);

create= or replace function f_get_x()
returns bigint
language plpgsql
sta= ble
as $function$
declare
=C2=A0 =C2=A0 l_result bigint;
begin<= br>=C2=A0 =C2=A0 select x into l_result from t_test;
=C2=A0 =C2=A0 --rai= se notice 'f_get_x() >> x=3D%', l_result;
=C2=A0 =C2=A0 --= raise notice 'f_get_x() >> xact=3D%', txid_current_if_assigne= d();
=C2=A0 =C2=A0 return l_result;
end;
$function$;

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


Now,= the case:
\set AUTOCOMMIT off
do
$$ begin
=C2=A0 =C2=A0 --rais= e notice 'do >> xact=3D%', txid_current_if_assigned();
=C2= =A0 =C2=A0 update t_test set x =3D 1;
=C2=A0 =C2=A0 --raise notice '= do >> xact=3D%', txid_current_if_assigned();
=C2=A0 =C2=A0 rai= se notice 'do >> x=3D%', f_get_x();
=C2=A0 =C2=A0 --raise = notice 'do >> xact=3D%', txid_current_if_assigned();
=C2= =A0 =C2=A0 call f_print_x(f_get_x());
end; $$;
NOTICE: =C2=A0do = >> x=3D1
NOTICE: =C2=A0f_print_x() >> x=3D0
DO

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

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

Ca= n you explain this behavior, please? Is it expected?

--
Victor Yegorov
--000000000000b3b3200619ffd45e--