Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

On 02/27/2017 09:08 AM, Sven R. Kunze wrote:
> 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?

Yes, but is not about timezone dependency, it is about the other
dependencies listed in the second and third points. Namely the datestyle
setting and magic strings e.g. 'now'

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


--
Adrian Klaver
adrian.klaver@aklaver.com