Feedback-ID: i76984098:Fastmail
Message-ID: <9e81428b-fb4c-4d72-9117-f18d262ead8c@aklaver.com>
Date: Thu, 10 Apr 2025 09:00:16 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
To: Nico Williams <nico@cryptonector.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, Olleg Samoylov <splarv@ya.ru>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru>
 <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at>
 <Z/bk5E6pqIBWTF3j@ubby> <c857b827-3a17-4600-aa98-d0710216ccfe@aklaver.com>
 <Z/foWNmbdDOWH6/M@ubby>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <Z/foWNmbdDOWH6/M@ubby>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/9e81428b-fb4c-4d72-9117-f18d262ead8c%40aklaver.com>
Precedence: bulk

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://www.postgresql.org/docs/current/catalog-pg-proc.html
>>
>> select proname, provolatile, prosrc  from pg_proc where proname='to_char';
>> [...]
> 
> I'm surprised to see that counted as docs, but good to know.

When in doubt consult the system catalogs.

My guess is the absence of specific volatility information in the data 
formatting functions is due to that information originating from a time 
before provolatile existed. Whereas the jsonb_.*_tz() functions are 
relatively new and where documented with knowledge of provolatile.


-- 
Adrian Klaver
adrian.klaver@aklaver.com