Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 9:23 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

> On Mon, Sep 28, 2020 at 2:44 PM Jesse Zhang <sbjesse(at)gmail(dot)com> wrote:
>
>> On Sun, Sep 27, 2020 at 7:52 PM Andy Fan wrote:
>> > On Mon, Sep 28, 2020 at 9:15 AM Tom Lane wrote:
>> >>
>> >> Yeah. It depends on the lc_time setting, and possibly also the
>> timezone
>> >> GUC. (Admittedly, common values of the format string would not have
>> >> any lc_time dependency, but the immutability property is not
>> fine-grained
>> >> enough to recognize that.)
>> >
>> > Thanks for your reply. Even it has something on GUC or lc_time setting,
>> suppose
>> > it should be decided at planning time. Do we have concerns about
>> changes
>> > between planning and execution?
>>
>> Immutable functions need to produce the same output for the same
>> argument values. None of the functions changed in the patch is
>> immutable: they are all stable because they all depend on GUC settings
>> (e.g. to_tsvector depends on default_text_search_config).
>>
>
> Thanks, how can I misunderstand Tom's comment above!!
>

To make data type formatting functions immutable you would need a third
input argument with the locale used for that specific function call, as
proposed in [1].

As for the original use case, in the documentation there is a tip about
using to_date() to handle input formats that cannot be converted by simple
casting, and that for most standard date/time formats a cast is the easier
way to do so [2], I may also add that is better performing in Postgres.

[1]
https://www.postgresql.org/message-id/01fe23b2-7779-d3ee-056a-074a7385e248%40mail.de
[2] https://www.postgresql.org/docs/current/functions-formatting.html

Regards,

Juan José Santamaría Flecha

>
>