On 2018.08.07 18:10, Tom Lane wrote:
I've had a to-do item to rewrite and improve the SQL function cache mechanism for a long time, but I hadn't thought it was high priority. Maybe it should be. In the meantime, I think you could dodge the issue by converting either level of function into plpgsql. Or just be careful about inline-ability of convenience wrappers.
I now remember I stumbled across this same problem last year when I needed an immutable unaccent() function for the purpose of creating a functional index. I simply wrapped unaccent(), which is stable, in an immutable custom function (and accepting the need to recreate the index in case when unaccent rules change). I can't remember the specifics but I was also surprised to learn that query execution plans were not optimized at all when the function was immutable and in some cases they were even slower than when the function was stable, quite contrary to all logic and to what the docs say. So I think it would be great if this issue were resolved in Postgres.
