Re: ERROR: functions in index expression must be marked IMMUTABLE

On 02/27/2017 07:03 AM, Sven R. Kunze wrote:
> On 27.02.2017 12:10, Geoff Winkless wrote:
>> On 27 February 2017 at 10:52, Sven R. Kunze <srkunze(at)mail(dot)de
>> <mailto:srkunze(at)mail(dot)de>>wrote:
>>
>>
>> So, what can I do to parse texts to date(times) in a safe manner?
>>
>>
>> You know best the format of your data; if you know that your date
>> field is always in a particular style and timezone, you can write a
>> function that can be considered safe to set IMMUTABLE, where a more
>> generic​ system todate function cannot.
>>
>> It might be sensible to call the function something that describes it
>> exactly, rather than my_to_date you could call it utc_yyyymmdd_todate
>> or something, just in case someone comes along later and sees an
>> immutable todate function and thinks they can use it for something else.
>>
>> Geoff
>
> Thanks, Geoff. It's not the most convenient way to define an index to
> define a function first and that all future queries need to use that
> very function in order to utilize the index. Though, it's the most
> safest and best documenting way.
>
>
> So, I got two additional questions:
>
> Why is this relevant for dates? I cannot see that dates are
> timezone-influenced.

Per Tom's post, see points 2 & 3:

"* some of them depend on the current timezone (but I don't believe
date_in does);

* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';

* all of them accept strings with time-varying values, such as 'now'
or 'today'.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.
"

>
> I still feel that a function is overkill for a simple text to date
> conversion. Couldn't there be an IMMUTABLE modifier for an expression to
> mark it as immutable?
>
>
> "SELECT '2007-02-02'::date;" just works. It would be great if one could
> define an index with the same ease. I already can see how our
> application developers need constant reminders that "in case of dates,
> use 'magic_function' first". If they don't, the application will suffer
> from bad performance.
>
>
> Thanks in advance for your replies.
>
> Regards,
> Sven

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com