On Wednesday, April 9, 2025, Olleg Samoylov <
splarv@ya.ru> wrote:
On 10.04.2025 01:08, Tom Lane wrote:
Yeah. The assumption is that you had a reason for marking the
function IMMUTABLE and you want the planner to treat it that way
even if it isn't really. (There are valid use-cases for that, for
instance if you want calls to the function to be constant-folded.)
regards, tom lane
Well, to_char(bigint, text) indeed not immutable, because in some pattern it uses get information from locale. For instance,'SLDG' patterns. But in case of
CREATE OR REPLACE FUNCTION formatted_num_immutable(p_summa bigint)
RETURNS text
LANGUAGE sql
IMMUTABLE STRICT
RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999'));
to_char do not use locale information in this pattern. So it is correct conclude that to_char is immutable with this pattern and formatted_num_immutable too. I did not lie to the planner.
So this is looked "strange", immutable function marked as immutable function can not be inlined, but exactly the same function marked as volatile do.
Yeah, the inlining is an optimization, and while it seems like it could perform more tests or maybe make slightly different/more adjustments, it really isn’t worth the development effort or runtime cost to do so. Make your function volatility match the most volatile function you internally call - constant input arguments don’t change this.
There is no reason to perform number formatting immutably - function call results involving table data are not memoized.
David J.
