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