Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 8:21 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>
> On Mon, Sep 28, 2020 at 7:15 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:
>> > On Mon, Sep 28, 2020 at 4:46 AM David Rowley <dgrowleyml(at)gmail(dot)com>
>> wrote:
>> >> Thanks for showing an interest in partition pruning. Unfortunately,
>> >> it's not possible to use stable functions to prune partitions during
>> >> planning.
>>
>> > Sigh.. I understand you now, I ignored the plan can be cached for later
>> use.
>> > Without that, we should be able to prune with stable function.
>>
>> No, that's still wrong. The contract for a stable function is that
>> its result won't change over execution of a single query; but that
>> says *execution*, not *planning and execution*.
>>
>
> I have a slightly different opinion about the impact of "cached the plan
> for later use will be wrong" now. Generic plan will never be partition
> pruned plan since we don't know which partition to prune at plan time.
> So for any cached plan, it is not a plan time partition pruned plan.
> Partition prune with stable expr is still unacceptable even this is not
> an issue but hope the snapshot issue will be the only one issue to
> fix in future for this direction. I'd like to know if I am wrong again.
>

Indeed I was wrong again. I'd like to end this thread with this
understanding fix.

prepare s as select * from measurement where logdate = $1 ;
execute s(now());

In this case, even if we run the planning time partition prune with a stable
function, we can still get the correct result (ignore the different
snapshot case).
since the generic plan includes all the partitions and just do initial
partition
prune case.

However if we create the prepared stmt like prepare s as select * from
measurement where logdate = now(); Then the cached plan should be
wrong. Actually this example is exactly the same as Daivd's example
at the beginning..

--
Best Regards
Andy Fan