Re: Partition prune with stable Expr

On Sun, Sep 27, 2020 at 7:52 PM Andy Fan wrote:
>
>
> On Mon, Sep 28, 2020 at 9:15 AM Tom Lane wrote:
>>
>> Andy Fan writes:
>> > Well, that's very interesting. Specific to my user case,
>> > SELECT * FROM p WHERE pkey = to_date('2018-12-13', 'yyyy-mm-dd)';
>> > p has 1500+ partitions and planning takes lots of time, which is so same
>> > with SELECT * FROM p WHERE pkey = '2018-12-13', however the planning
>> > time difference is so huge, that doesn't make sense in human view. Can
>> > we do something for that? to_date(text, text) should be a "immutable"
>> > function IMO. Does that have a semantic issue or other issues?
>>
>> 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.)
>>
>> regards, tom lane
>
>
> 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?

Planner can be called at prepared statement creation time, like

PREPARE yolo() AS SELECT * FROM foo WHERE pk = to_date(...);

Here, there's an arbitrary gap between planning time, and execution.

>
> The attached patch marked some common formatting function as immutable,
> only one partition prune test case needed fixing because of this. I only changed
> to_char/to_date/to_timestamp, however the whole list is below. I can change
> all of them if needed.
>
> proname | count
> -----------------+-------
> to_ascii | 3
> to_char | 8
> to_date | 1
> to_hex | 2
> to_json | 1
> to_jsonb | 1
> to_number | 1
> to_regclass | 1
> to_regcollation | 1
> to_regnamespace | 1
> to_regoper | 1
> to_regoperator | 1
> to_regproc | 1
> to_regprocedure | 1
> to_regrole | 1
> to_regtype | 1
> to_timestamp | 2
> to_tsquery | 2
> to_tsvector | 6
> (19 rows)
>
This patch is ridiculous.

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