public inbox for [email protected]
help / color / mirror / Atom feedscalar plpgsql functions and their stability flags
2+ messages / 2 participants
[nested] [flat]
* scalar plpgsql functions and their stability flags
@ 2024-05-26 13:20 Victor Dobrovolsky <[email protected]>
2024-05-26 14:27 ` Re: scalar plpgsql functions and their stability flags Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Victor Dobrovolsky @ 2024-05-26 13:20 UTC (permalink / raw)
To: [email protected]
Good day experts...
Question on scalar plpgsql functions stability flags (immutable, stable)
regarding how it works in sql queries.
It is clear that for immutable/stable functions with constant parameters,
query planner could/should calculate value in a parse time and use it
directly in query, or at least once per query.
But it is unclear for me what exactly should/can happens when parameters
are bounded not to constant values but to query fields.
In such a case there could be some caching mechanics involved for
parameters combinations and result values.
Like building a hash table for that or something similar.
Can someone give me guidance on this matter.
What limits the usefulness of such a mechanism, if it exists.
Thank you.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: scalar plpgsql functions and their stability flags
2024-05-26 13:20 scalar plpgsql functions and their stability flags Victor Dobrovolsky <[email protected]>
@ 2024-05-26 14:27 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-05-26 14:27 UTC (permalink / raw)
To: Victor Dobrovolsky <[email protected]>; +Cc: [email protected]
Victor Dobrovolsky <[email protected]> writes:
> It is clear that for immutable/stable functions with constant parameters,
> query planner could/should calculate value in a parse time and use it
> directly in query, or at least once per query.
Immutable, yes, stable, no.
Awhile back there was a draft patch to cache outputs of stable functions
after running them once in a query, but I don't think anyone's still
working on that. IIRC we were having a hard time convincing ourselves
that the extra bookkeeping would pay for itself.
> But it is unclear for me what exactly should/can happens when parameters
> are bounded not to constant values but to query fields.
> In such a case there could be some caching mechanics involved for
> parameters combinations and result values.
> Like building a hash table for that or something similar.
No such mechanism exists. Again, there would be a lot of tradeoffs
involved and it's difficult to say if it'd be a win.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-05-26 14:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-26 13:20 scalar plpgsql functions and their stability flags Victor Dobrovolsky <[email protected]>
2024-05-26 14:27 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox