Re: Partition prune with stable Expr

On Mon, 28 Sep 2020 at 08:59, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> I find we can't prune partitions in the planner if the qual is a stable function.

> IMO, we should do it. Why not?

Thanks for showing an interest in partition pruning. Unfortunately,
it's not possible to use stable functions to prune partitions during
planning.

NOW() is one example of a function that's stable, but the return value
will change over time. If we used the return value of that to perform
partition pruning then we'd end up with a plan that's wrong over time.

Here's an example:

create table rp (t timestamp) partition by range(t);
create table rp1 partition of rp for values from ('now'::timestamp) to
('now'::timestamp + '1 min'::interval);
create table rp2 partition of rp for values from ('now'::timestamp +
'1 min'::interval) to ('now'::timestamp + '2 min'::interval);
insert into rp select t from generate_Series('now'::timestamp,
'now'::timestamp + '1 min 59 sec'::interval, '1 sec'::interval) t;

prepare q1 as select count(*) from rp where t > now() and t < now() +
'10 sec'::interval;

Now, if you run the following command with your patch, it'll prune the
rp2 partition as it's not required for the WHERE clause (at the time
we planned). However, just wait 1 minute and execute the plan again.
Oops, my rows vanished!

execute q1; select pg_sleep(60); execute q1;

The 2nd execute should have returned 10 rows, the same as the first
(assuming you executed that directly after creating the tables)

Run-time partition pruning was invented just for this purpose.

David