Thread: Are operations on real values IMMUTABLE or STABLE?
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'm a bit hazy on exactly when some operations shift from IMMUTABLE to STABLE. For example, it seems fair that many time/date operations are not IMMUTABLE because they vary based on the current time zone. Likewise, I think that text operations are generally not IMMUTABLE since collations vary across versions and platforms.
Any clarification would be appreciated. I've been googling around and checking the archives, but haven't found these specific details addressed, so far.
Thank you!
-----------------------------------
-- array_sum(int8[]) : int8
-----------------------------------
CREATE OR REPLACE FUNCTION tools.array_sum(array_in int8[])
RETURNS int8 AS
$BODY$
SELECT SUM(element) AS result
FROM UNNEST(array_in) AS element;
$BODY$
LANGUAGE sql
IMMUTABLE;
-- Add a comment to describe the function
COMMENT ON FUNCTION tools.array_sum(int8[]) IS
'Sum an int8[] array.';
-- Set the function's owner to USER_BENDER
ALTER FUNCTION tools.array_sum(int8[]) OWNER TO user_bender;
-----------------------------------
-- array_sum(real[]]) : real
-----------------------------------
CREATE OR REPLACE FUNCTION tools.array_sum(array_in real[])
RETURNS real AS
$BODY$
SELECT SUM(element) AS result
FROM UNNEST(array_in) AS element;
$BODY$
LANGUAGE sql
STABLE; -- Decimal number types seem to change across versions and chips?
-- Add a comment to describe the function
COMMENT ON FUNCTION tools.array_sum(real[]) IS
'Sum an real[] array.';
-- Set the function's owner to USER_BENDER
ALTER FUNCTION tools.array_sum(real[]) OWNER TO user_bender;
I'm a bit hazy on exactly when some operations shift from IMMUTABLE to STABLE. For example, it seems fair that many time/date operations are not IMMUTABLE because they vary based on the current time zone. Likewise, I think that text operations are generally not IMMUTABLE since collations vary across versions and platforms.
Any clarification would be appreciated. I've been googling around and checking the archives, but haven't found these specific details addressed, so far.
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.
Thank you!
-----------------------------------
-- array_sum(int8[]) : int8
-----------------------------------
CREATE OR REPLACE FUNCTION tools.array_sum(array_in int8[])
RETURNS int8 AS
$BODY$
SELECT SUM(element) AS result
FROM UNNEST(array_in) AS element;
$BODY$
LANGUAGE sql
IMMUTABLE;
-- Add a comment to describe the function
COMMENT ON FUNCTION tools.array_sum(int8[]) IS
'Sum an int8[] array.';
-- Set the function's owner to USER_BENDER
ALTER FUNCTION tools.array_sum(int8[]) OWNER TO user_bender;
-----------------------------------
-- array_sum(real[]]) : real
-----------------------------------
CREATE OR REPLACE FUNCTION tools.array_sum(array_in real[])
RETURNS real AS
$BODY$
SELECT SUM(element) AS result
FROM UNNEST(array_in) AS element;
$BODY$
LANGUAGE sql
STABLE; -- Decimal number types seem to change across versions and chips?
-- Add a comment to describe the function
COMMENT ON FUNCTION tools.array_sum(real[]) IS
'Sum an real[] array.';
-- Set the function's owner to USER_BENDER
ALTER FUNCTION tools.array_sum(real[]) OWNER TO user_bender;
Morris de Oryx <morrisdeoryx@gmail.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
I think you're overthinking it.
Moi? Never happens ;-)
Fantastic answer, thanks very much for giving me all of these details. Coming from you, I'll take it as authoritative and run with it.