Message-ID: <79ea991d-e7d9-17f3-da51-982d74a5c37e@postgrespro.ru>
Date: Thu, 15 Jun 2023 19:33:52 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: When IMMUTABLE is not.
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "chap@anastigmatix.net" <chap@anastigmatix.net>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
References: <389c986d-fbb4-c644-9280-db7836af7ca9@postgrespro.ru>
 <2111925.1686835298@sss.pgh.pa.us>
 <69234e25e34e68425f49630f79ef9991@anastigmatix.net>
 <4c32f84bc097dee0081e4cc3e733ef8d@anastigmatix.net>
 <CAKFQuwbcsPwm_iiE8p5TYZ=2AEnt4LHpCnjjHYoCRAuZk9tzFw@mail.gmail.com>
 <2131668.1686840541@sss.pgh.pa.us>
From: Yura Sokolov <y.sokolov@postgrespro.ru>
In-Reply-To: <2131668.1686840541@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/79ea991d-e7d9-17f3-da51-982d74a5c37e%40postgrespro.ru>
Precedence: bulk


15.06.2023 17:49, Tom Lane пишет:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> The failure to find and execute the function code itself is not a failure
>> mode that these markers need be concerned with.  Assuming one can execute
>> the function an immutable function will give the same answer for the same
>> input for all time.
> The viewpoint taken in the docs I mentioned is that an IMMUTABLE
> marker is a promise from the user to the system about the behavior
> of a function.  While the system does provide a few simple tools
> to catch obvious errors and to make it easier to write functions
> that obey such promises, it's mostly on the user to get it right.
>
> In particular, we've never enforced that an immutable function can't
> call non-immutable functions.  While that would seem like a good idea
> in the abstract, we've intentionally not tried to do it.  (I'm pretty
> sure there is more than one round of previous discussions of the point
> in the archives, although locating relevant threads seems hard.)
> One reason not to is that polymorphic functions have to be marked
> with worst-case volatility labels.  There are plenty of examples of
> functions that are stable for some input types and immutable for
> others (array_to_string, for instance); but the marking system can't
> represent that so we have to label them stable.  Enforcing that a
> user-defined immutable function can't use such a function might
> just break things for no gain.

"Stable vs Immutable" is much lesser problem compared to "ReadOnly vs 
Volatile".

Executing fairly read-only function more times than necessary (or less 
times),
doesn't modify data in unexpecting way.

But executing immutable/stable function, that occasionally modifies 
data, could
lead to different unexpected effects due to optimizer decided to call 
them more
or less times than query assumes.

Some vulnerabilities were present due to user defined functions used in 
index
definitions started to modify data. If "read-only" execution were forced 
in index
operations, those issues couldn't happen.

 > it's mostly on the user to get it right.

It is really bad premise. Users does strange things and aren't expected 
to be
professionals who really understand whole PostgreSQL internals.

And it is strange to hear it at the same time we don't allow users to do 
query hints
since "optimizer does better" :-D

Ok, I'd go and cool myself. Certainly I don't get some point.