From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Sven R(dot) Kunze" <srkunze(at)mail(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Date: | 2017-02-27 17:17:30 |
Message-ID: | d63ed471-18ee-c52c-e062-4194056edf16@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2017-02-27 18:00:39 | Re: hight cpu %sy usage |
Previous Message | Sven R. Kunze | 2017-02-27 17:08:11 | Re: ERROR: functions in index expression must be marked IMMUTABLE |