MIME-Version: 1.0
References: <CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com>
 <CAM-sOH80Z=OqYBWgYP=BDGLbxQ72wkC9=tZ-vRKxkmKkhTD7MQ@mail.gmail.com>
In-Reply-To: <CAM-sOH80Z=OqYBWgYP=BDGLbxQ72wkC9=tZ-vRKxkmKkhTD7MQ@mail.gmail.com>
From: Victor Yegorov <vyegorov@gmail.com>
Date: Mon, 3 Jun 2024 21:15:07 +0300
Message-ID: <CAGnEbojf5Awm862ghvooLku6mm0m4yF60PyHQxCinM8pn01sbw@mail.gmail.com>
Subject: Re: Unexpected results from CALL and AUTOCOMMIT=off
To: Pierre Forstmann <pierre.forstmann@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d71b56061a0050a0"
Archived-At: <https://www.postgresql.org/message-id/CAGnEbojf5Awm862ghvooLku6mm0m4yF60PyHQxCinM8pn01sbw%40mail.gmail.com>
Precedence: bulk

--000000000000d71b56061a0050a0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

=D0=BF=D0=BD, 3 =D0=B8=D1=8E=D0=BD. 2024=E2=80=AF=D0=B3. =D0=B2 20:40, Pier=
re Forstmann <pierre.forstmann@gmail.com>:

> You declared function f_get_x as stable which means:
>
> =E2=80=A6
>
> If you remove stable from function declaration, it works as expected:
>

Well, I checked
https://www.postgresql.org/docs/current/xfunc-volatility.html
There's a paragraph describing why STABLE (and IMMUTABLE) use different
snapshots:

> For functions written in SQL or in any of the standard procedural
languages, there is a second important property determined by the
volatility category, namely the visibility of any data changes that have
been made by the SQL command that is calling the function. A > VOLATILE
function will see such changes, a STABLE or IMMUTABLE function will not.
This behavior is implemented using the snapshotting behavior of MVCC (see
Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as
of the start of the
> calling query, whereas VOLATILE functions obtain a fresh snapshot at the
start of each query they execute.

But later, docs state, that

> Because of this snapshotting behavior, a function containing only SELECT
commands can safely be marked STABLE, even if it selects from tables that
might be undergoing modifications by concurrent queries. PostgreSQL will
execute all commands of a STABLE function using the snapshot established
for the calling query, and so it will see a fixed view of the database
throughout that query.

And therefore I assume STABLE should work in this case. Well, it seems not
to.

I assume there's smth to do with implicit BEGIN issued in non-AUTOCOMMIT
mode and non-atomic DO block behaviour.


--=20
Victor Yegorov

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

<div dir=3D"ltr"><div dir=3D"ltr">=D0=BF=D0=BD, 3 =D0=B8=D1=8E=D0=BD. 2024=
=E2=80=AF=D0=B3. =D0=B2 20:40, Pierre Forstmann &lt;<a href=3D"mailto:pierr=
e.forstmann@gmail.com">pierre.forstmann@gmail.com</a>&gt;:<br></div><div cl=
ass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><div>You declared function f_get_x as stable which means:</div>=
<div><br></div><div>=E2=80=A6</div><div><br></div><div>If you remove stable=
 from function declaration, it works as expected:</div></div></blockquote><=
div><br>Well, I checked=C2=A0<a href=3D"https://www.postgresql.org/docs/cur=
rent/xfunc-volatility.html">https://www.postgresql.org/docs/current/xfunc-v=
olatility.html</a><br>There&#39;s a paragraph describing why STABLE (and IM=
MUTABLE) use different snapshots:<br><br>&gt; For functions written in SQL =
or in any of the standard procedural languages, there is a second important=
 property determined by the volatility category, namely the visibility of a=
ny data changes that have been made by the SQL command that is calling the =
function. A &gt; VOLATILE function will see such changes, a STABLE or IMMUT=
ABLE function will not. This behavior is implemented using the snapshotting=
 behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use a sn=
apshot established as of the start of the<br>&gt; calling query, whereas VO=
LATILE functions obtain a fresh snapshot at the start of each query they ex=
ecute.<br><br>But later, docs state, that<br><br>&gt; Because of this snaps=
hotting behavior, a function containing only SELECT commands can safely be =
marked STABLE, even if it selects from tables that might be undergoing modi=
fications by concurrent queries. PostgreSQL will execute all commands of a =
STABLE function using the snapshot established for the calling query, and s=
o it will see a fixed view of the database throughout that query.<br><br>An=
d therefore I assume STABLE should work in this case. Well, it seems not to=
.<br><br>I assume there&#39;s smth to do with implicit BEGIN issued in non-=
AUTOCOMMIT mode and non-atomic DO block behaviour.</div></div><br clear=3D"=
all"><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><d=
iv dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Victor Yegor=
ov</div></div></div></div>

--000000000000d71b56061a0050a0--