On Tue, Jul 16, 2024 at 11:57 AM Joe Conway <m...@joeconway.com> wrote:
> > > 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). > Under-specification here doesn't make the meaning of stable incorrect. We don't have anything that guarantees stability at the transaction scope because I don't think it can be guaranteed there without considering whether said transaction is read-committed, repeatable read, or serializable. The function itself can promise more but the marker seems correctly scoped for how the system uses it in statement optimization. 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". > > I'd be content cutting "forever" down to "within a given server configuration". Then just note that immutable functions can depend implicitly on external server characteristics and so when moving data between servers re-evaluation of immutable functions may be necessary. Not so bad for indexes. A bit more problematic for generated values. I'm not against adding metadata options here but for internal functions comments and documentation can work. For user-defined functions I have my doubts on how trustworthy they would end up being. For the original question, I suggest continuing behaving per "A" and work on making it more clear to users what that means in terms of server upgrades. If we do add metadata to reflect our reality I'd settle on a generic "STATIC" marker that can be used on those functions the rely on real world state, whether we are directly calling into the system (e.g., hashing) or have chosen to provide the state access management ourselves (e.g., unicode). When we do take control we should have a goal of allowing for a given external dependency version to exist in many PostgreSQL versions and give the DBA the choice of when to move individual databases from one version to the next. Possibly dropping the dependency version support alongside the dropping of support of the major version it first appeared in. Not keeping up with external dependency versions just punishes new users by forbidding them a tool permanently, as well as puts us out-of-step with those dependency development groups, to save existing users some short-term pain. Being able to deal with that pain at a time different than the middle of a major version upgrade, one database at a time, gives those existing users reasonable options. David J.