Re: no partition pruning when partitioning using array type

On 2018-Jul-11, Amit Langote wrote:

> On 2018/07/11 13:12, Alvaro Herrera wrote:
> > On 2018-Jul-11, Amit Langote wrote:
> >
> >> What's the solution here then? Prevent domains as partition key?
> >
> > Maybe if a domain is used in a partition key somewhere, prevent
> > constraints from being added?
>
> Maybe, but I guess you mean only prevent adding such a constraint
> after-the-fact.

Yeah, any domain constraints added before won't be a problem. Another
angle on this problem is to verify partition bounds against the domain
constraint being added; if they all pass, there's no reason to reject
the constraint.

But I worry that Tom was using domain constraints as just an example of
a more general problem that we can get into.

> If a domain has a constraint before creating any partitions based on the
> domain, then partition creation command would check that the partition
> bound values satisfy those constraints.

Of course.

> > Another thing worth considering: are you prevented from dropping a
> > domain that's used in a partition key? If not, you get an ugly message
> > when you later try to drop the table.
>
> Yeah, I was about to write a message about that. I think we need to teach
> RemoveAttributeById, which dependency.c calls when dropping the
> referencing domain with cascade option, to abort if the attribute passed
> to it belongs to the partition key of the input relation.

Actually, here's another problem: why are we letting a column on a
domain become partition key, if you'll never be able to create a
partition? It seems that for pg11 the right reaction is to check
the partition key and reject this case.

I'm not sure of this implementation -- I couldn't find any case where we
reject the deletion on the function called from doDeletion (and we don't
have a preliminary phase on which to check for this, either). Maybe we
need a pg_depend entry to prevent this, though it seems a little silly.
Maybe we should add a preliminary verification phase in
deleteObjectsInList(), where we invoke object-type-specific checks.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services