пн, 3 июн. 2024 г. в 20:40, Pierre Forstmann <pierre.forstmann@xxxxxxxxx>:
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
