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

On 27.02.2017 16:37, Adrian Klaver wrote:
> On 02/27/2017 07:03 AM, Sven R. Kunze wrote:
>> Why is this relevant for dates? I cannot see that dates are
>> timezone-influenced.
>
> Per Tom's post, see points 2 & 3:

Maybe, I am on a completely wrong track here, but to me dates still
don't look timezone dependent. They are just dates and not times, aren't
they?

> "* 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.
> "

Reading this through again, I got an idea:

Wouldn't it be possible to provide an immutable variant of to_timestamp
and to_date with a third parameter to specify the otherwise
setting-dependent timezone?

>> 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?

Any thoughts on this?

>> "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.

Best regards,
Sven