Re: no partition pruning when partitioning using array type

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: no partition pruning when partitioning using array type
Date: 2018-07-11 17:33:37
Message-ID: 20180711173337.oe2ze7ab2foh6egx@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-07-11 17:34:49 Re: In pageinspect, perform clean-up after testing gin-related functions
Previous Message Tom Lane 2018-07-11 17:26:40 Re: [PATCH] btree_gist: fix union implementation for variable length columns