public inbox for [email protected]  

help / color / mirror / Atom feed

From: Victor Yegorov <[email protected]>
To: Pierre Forstmann <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Unexpected results from CALL and AUTOCOMMIT=off
Date: Mon, 3 Jun 2024 21:15:07 +0300
Message-ID: <CAGnEbojf5Awm862ghvooLku6mm0m4yF60PyHQxCinM8pn01sbw@mail.gmail.com> (raw)
In-Reply-To: <CAM-sOH80Z=OqYBWgYP=BDGLbxQ72wkC9=tZ-vRKxkmKkhTD7MQ@mail.gmail.com>
References: <CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com>
	<CAM-sOH80Z=OqYBWgYP=BDGLbxQ72wkC9=tZ-vRKxkmKkhTD7MQ@mail.gmail.com>

пн, 3 июн. 2024 г. в 20:40, Pierre Forstmann <[email protected]>:

> You declared function f_get_x as stable which means:
>
> …
>
> 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.


-- 
Victor Yegorov

view thread (6+ messages)  latest in thread

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], [email protected]
  Subject: Re: Unexpected results from CALL and AUTOCOMMIT=off
  In-Reply-To: <CAGnEbojf5Awm862ghvooLku6mm0m4yF60PyHQxCinM8pn01sbw@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