Re: BUG #16745: delete does not prune partitions on declarative partitioned table

On Thu, 26 Nov 2020 at 11:34, Christian <akattunga(at)gmail(dot)com> wrote:
> Ok so this is a known issue.

The reason you don't get the behaviour that you'd like is that there
is no run-time partition pruning for UPDATE/DELETE. The current_date
cannot be evaluated during query planning, so plan-time partition
pruning cannot be done then.

If you know for certain that you or your client never prepare queries,
meaning, planning always takes place just before execution, then you
might be able to get away with:

delete FROM FAC_ITEM WHERE FCODDIST='' AND FSUCURS=1 AND FFECHAI =
'today'::date;

The 'today'::date will be evaluated earlier enough that the planner
will be able to perform partition pruning using the current date.

Just be aware, if you do cache plans somewhere the date will be cached
along with them. Things will start going badly for you after midnight.

David