Re: Are operations on real values IMMUTABLE or STABLE?

Morris de Oryx <morrisdeoryx(at)gmail(dot)com> writes:
> I've got a small question about marking functions working with decimal
> number types as either IMMUTABLE or STABLE. Below are a pair of trivial
> functions that show what I'm guessing. An int8/int8[] seems like it's going
> to be immutable forever. However, decimal types aren't quite so crisp and
> consistent. Does this mean that I need to mark such a function as
> STABLE instead
> of IMMUTABLE, like below?

I think you're overthinking it. We have no hesitation about marking
built-in floating-point functions as immutable, so if you're worried
about some other machine hypothetically delivering different results,
you're in trouble anyway. (In practice, the whole world is supposedly
compliant with IEEE float arithmetic, so such cases shouldn't arise.)

> Ah, and I have no clue how much difference it even makes to mark a function
> as IMMUTABLE instead of STABLE. If the difference is more theoretical than
> practical, I can feel comfortable using STABLE, when unclear.

It's entirely not theoretical. The system won't let you use a
non-IMMUTABLE function in an index definition or generated column,
and there are significant query-optimization implications as well.
So generally people tend to err on the side of marking things
IMMUTABLE if it's at all plausible to do so. In the worst case
you might end up having to reindex, or rebuild generated columns,
should the function's behavior actually change. Frequently that
risk is well worth taking.

regards, tom lane