Re: Partition prune with stable Expr

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

In particular, the canonical example of a stable function is one
whose result depends on a database query. The reason it can be
considered stable is that within a single outer query, the MVCC
snapshot it's used with won't change. But we take a new snapshot
(later than the planner's snapshot) when beginning execution.

Somebody (Robert Haas, if memory serves, which it might not)
tried to change that a few years ago. It blew up pretty well,
and was eventually reverted, because of undesirable side-effects
on user-visible query semantics. You'd have to check the archives
for details.

It's possible that we could make that work differently in serializable
mode, thanks to the longer persistence of snapshots. Not sure that
it'd be desirable for planning to work differently in serializable
mode, though.

regards, tom lane