Tom Lane:
If you err in the other direction, you don't get slapped on the
wrist that way. We're willing to inline VOLATILE functions,
for instance, whether or not the contained expression is volatile.
Similarly for STRICT, and I think parallel safety as well.
So my own habit when writing a SQL f
On Thu, Apr 10, 2025 at 10:51 PM Tom Lane wrote:
> Merlin Moncure writes:
> > I guess the real problems here are lack of feedback on a number of
> fronts:
> > *) the server knows the function is not immutable but lets you create it
> > anyway, even though it can have negative downstream conseque
On Fri, Apr 11, 2025 at 5:52 AM Tom Lane wrote:
> Merlin Moncure writes:
> > I guess the real problems here are lack of feedback on a number of fronts:
> > *) the server knows the function is not immutable but lets you create it
> > anyway, even though it can have negative downstream consequences
On Fri, Apr 11, 2025 at 07:15:44AM +0200, Laurenz Albe wrote:
> On Thu, 2025-04-10 at 22:18 -0500, Merlin Moncure wrote:
> > Facts. This is black magic. This has come up over and over.
>
> Perhaps it would help to add the excellent information from the Wiki to the
> documentation:
> https://w
On Thu, 2025-04-10 at 22:18 -0500, Merlin Moncure wrote:
> Facts. This is black magic. This has come up over and over.
Perhaps it would help to add the excellent information from the Wiki to the
documentation:
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
Yours,
Laurenz Albe
On Thursday, April 10, 2025, Tom Lane wrote:
> Merlin Moncure writes:
> > I guess the real problems here are lack of feedback on a number of
> fronts:
> > *) the server knows the function is not immutable but lets you create it
> > anyway, even though it can have negative downstream consequences
Merlin Moncure writes:
> I guess the real problems here are lack of feedback on a number of fronts:
> *) the server knows the function is not immutable but lets you create it
> anyway, even though it can have negative downstream consequences
That's debatable I think. If you know what you're doin
On Thu, Apr 10, 2025 at 10:59 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Apr 10, 2025 at 8:49 AM Nico Williams
> wrote:
>
>> On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote:
>> > On 4/9/25 14:21, Nico Williams wrote:
>> > > That to_char is not immutable is n
On Wednesday, April 9, 2025, Olleg Samoylov 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
On 4/9/25 22:33, Olleg Samoylov 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 ca
On Thu, Apr 10, 2025 at 8:49 AM Nico Williams wrote:
> On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote:
> > On 4/9/25 14:21, Nico Williams wrote:
> > > That to_char is not immutable is not documented though. Though it's
> > > clear when looking at the docs for the `jsonb_.*_tz()` f
On 4/10/25 08:48, Nico Williams wrote:
On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote:
On 4/9/25 14:21, Nico Williams wrote:
That to_char is not immutable is not documented though. Though it's
clear when looking at the docs for the `jsonb_.*_tz()` functions.
From here:
https
On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote:
> On 4/9/25 14:21, Nico Williams wrote:
> > That to_char is not immutable is not documented though. Though it's
> > clear when looking at the docs for the `jsonb_.*_tz()` functions.
>
> From here:
>
> https://www.postgresql.org/docs/
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-fold
On Wed, Apr 09, 2025 at 10:50:00PM +0200, Laurenz Albe wrote:
> The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE.
Q: Why would to_char() not be IMMUTABLE?
A: Because it makes use of locales, and I guess the guc-timezone GUC,
which could change if the expression is ul
"David G. Johnston" writes:
> On Wed, Apr 9, 2025 at 1:50 PM Laurenz Albe
> wrote:
>> The IMMUTABLE function cannot be inlined because to_char() is not
>> IMMUTABLE.
> So, the punishment for lying about the volatility of one's function is to
> prohibit it from being inlined even in a case where
On 4/9/25 14:21, Nico Williams wrote:
On Wed, Apr 09, 2025 at 10:50:00PM +0200, Laurenz Albe wrote:
The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE.
Q: Why would to_char() not be IMMUTABLE?
A: Because it makes use of locales, and I guess the guc-timezone GUC,
w
On Wed, 2025-04-09 at 19:12 +0300, Olleg Samoylov wrote:
> PostgreSQL 17.4
>
> 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'));
>
> CREATE OR REPLACE FUNCT
On Wed, Apr 9, 2025 at 1:50 PM Laurenz Albe
wrote:
> If you use EXPLAIN (VERBOSE), you will see that the function gets inlined
> in the fast case.
>
> That saves the overhead of a function call.
>
> The IMMUTABLE function cannot be inlined because to_char() is not
> IMMUTABLE.
>
So, the punishme
PostgreSQL 17.4
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'));
CREATE OR REPLACE FUNCTION formatted_num_stable(p_summa bigint)
RETURNS text
LANGUAGE sql
STABLE
20 matches
Mail list logo
