On 7/16/24 13:42, Jeff Davis wrote:
The IMMUTABLE marker for functions is quite simple on the surface, but
could be interpreted a few different ways, and there's some historical
baggage that makes it complicated.
There are a number of ways in which IMMUTABLE functions can change
behavior:
1. Updating or moving to a different OS affects all collations that use
the libc provider (other than "C" and "POSIX", which don't actually use
libc). LOWER(), INITCAP(), UPPER() and pattern matching are also
affected.
2. Updating ICU affects the collations that use the ICU provider.
ICU_UNICODE_VERSION(), LOWER(), INITCAP(), UPPER() and pattern matching
are also affected.
3. Moving to a different database encoding may affect collations that
use the "C" or "POSIX" locales in the libc provider (NB: those locales
don't actually use libc).
4. A PG Unicode update may change the results of functions that depend
on Unicode. For instance, NORMALIZE(), UNICODE_ASSIGNED(), and
UNICODE_VERSION(). Or, if using the new builtin provider's "C.UTF-8"
locale in version 17, LOWER(), INITCAP(), UPPER(), and pattern matching
(NB: collation itself is not affected -- always code point order).
5. If a well-defined IMMUTABLE function produces the wrong results, we
may fix the bug in the next major release.
6. The GUC extra_float_digits can change the results of floating point
text output.
7. A UDF may be improperly marked IMMUTABLE. A particularly common
variant is a UDF without search_path specified, which is probably not
truly IMMUTABLE.
(more I'm sure, please add to list...)
#1 and #2 have been discussed much more than the rest, but I think it's
worthwhile to enumerate the other problems even if the impact is a lot
lower.
Noah seemed particularly concerned[1] about #4, so I'll start off by
discussing that. Here's a brief history (slightly confusing because the
PG and Unicode versions are similar numbers):
PG13: Unicode 13.0 and NORMALIZE() is first exposed as a SQL function
PG15: Unicode updated to 14.0
PG16: Unicode updated to 15.0
PG17: Unicode updated to 15.1, UNICODE_ASSIGNED(), UNICODE_VERSION()
and builtin "C.UTF-8" locale are introduced
To repeat, these Unicode updates do not affect collation itself, they
affect affect NORMALIZE(), UNICODE_VERSION(), and UNICODE_ASSIGNED().
If using the builtin "C.UTF-8" locale, they also affect LOWER(),
INITCAP(), UPPER(), and pattern matching. (NB: the builtin collation
provider hasn't yet gone through any Unicode update.)
There are two alternative philosophies:
A. By choosing to use a Unicode-based function, the user has opted in
to the Unicode stability guarantees[2], and it's fine to update Unicode
occasionally in new major versions as long as we are transparent with
the user.
B. IMMUTABLE implies some very strict definition of stability, and we
should never again update Unicode because it changes the results of
IMMUTABLE functions.
We've been following (A), and that's the defacto policy today[3][4].
Noah and Laurenz argued[5] that the policy starting in version 18
should be (B). Given that it's a policy decision that affects more than
just the builtin collation provider, I'd like to discuss it more
broadly outside of that subthread.
On the general topic, we have these definitions in the fine manual:
8<-----------------
A VOLATILE function can do anything, ... A query using a volatile
function will re-evaluate the function at every row where its value is
needed.
A STABLE function cannot modify the database and is guaranteed to return
the same results given the same arguments for all rows within a single
statement...
An IMMUTABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments forever.
8<-----------------
As Jeff points out, the IMMUTABLE definition has never really been true.
Even the STABLE is not quite right, as there are at least some STABLE
functions that will return the same value for multiple statements if
they are within a transaction block (e.g. "now()" -- TBH I don't
remember offhand if that is true for all stable functions).
In any case, there is quite a gap between "forever" and "single
statement". Perhaps we need to have more volatility categories, with
guarantees that lie somewhere between the two, and allow those to be
used like we do IMMUTABLE except with appropriate warning labels. E.g.
something ("STABLE_VERSION"?) to mean "forever within a major version
lifetime" and something ("STABLE_SYSTEM?") to mean "as long as you don't
upgrade your OS".
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
