Re: [RFC] [PATCH] Flexible "partition pruning" hook

On 2019-02-26 19:06, Mike Palmiotto wrote:
> The desired effect would be to have `SELECT * from test.partpar;`
> return check only the partitions where username can see any row in the
> table based on column b. This is applicable, for instance, when a
> partition of test.partpar (say test.partpar_b2) is given a label with
> `SECURITY LABEL on TABLE test.partpar_b2 IS 'foo';` which is exactly
> the same as the b column for every row in said partition. Using this
> hook, we can simply check the table label and kick the entire
> partition out early on. This should greatly improve performance for
> the case where you can enforce that the partition SECURITY LABEL and
> the b column are the same.

To rephrase this: You have a partitioned table, and you have a RLS
policy that hides certain rows, and you know based on your business
logic that under certain circumstances entire partitions will be hidden,
so they don't need to be scanned. So you want a planner hook that would
allow you to prune those partitions manually.

That sounds pretty hackish to me. We should give the planner and
executor the appropriate information to make these decisions, like an
additional partition constraint. If this information is hidden in
user-defined functions in a way that cannot be reasoned about, who is
enforcing these constraints and how do we know they are actually correct?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services