Re: plans for PostgreSQL 12

2018-06-04 9:24 GMT+02:00 Heikki Linnakangas <hlinnaka(at)iki(dot)fi>:

> On 04/06/18 09:12, Pavel Stehule wrote:
>
>> 2018-06-04 8:35 GMT+02:00 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>>
>>>
>>> Sounds good. I think this would need to be restricted by operator and
>>> datatype, since in general you won't know if the datatype functions
>>> need a snapshot or not. Immutable functions for the operators ought to
>>> do it, but I think that might not be enough.
>>>
>>
>> It requires introduction of new "safe" functions (& operators). Immutable
>> functions are not enough safe.
>>
>> CREATE OR REPLACE FUNCTION public.fx()
>> RETURNS integer
>> LANGUAGE plpgsql
>> IMMUTABLE
>> AS $function$
>> BEGIN
>> RETURN (SELECT count(*) FROM pg_class);
>> END;
>> $function$
>>
>> postgres=# SELECT fx();
>> ┌─────┐
>> │ fx │
>> ╞═════╡
>> │ 343 │
>> └─────┘
>> (1 row)
>>
>
> That function is incorrectly marked as IMMUTABLE. In that situation, it's
> enough that we throw a sane error like "ERROR: no snapshot available".
>

Yes, it is incorrect mark. Unfortunately - this is often workaround for
wrong estimations - so I afraid, in this case, your proposed fix breaks lot
of applications.

Regards

Pavel

> - Heikki
>