Re: no partition pruning when partitioning using array type

On 2018/07/26 1:41, Alvaro Herrera wrote: > On 2018-Jul-12, Amit Langote wrote: >> On 2018/07/12 2:33, Alvaro Herrera wrote: >>> 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

Re: no partition pruning when partitioning using array type

On 2018-Jul-12, Amit Langote wrote: > On 2018/07/12 2:33, Alvaro Herrera wrote: > > 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

Re: no partition pruning when partitioning using array type

On 2018/07/12 2:33, Alvaro Herrera wrote: > 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

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

Re: no partition pruning when partitioning using array type

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

Re: no partition pruning when partitioning using array type

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? Another thing worth considering: are you prevented from dropping a domain that's used in a

Re: no partition pruning when partitioning using array type

On 2018/07/11 4:50, Alvaro Herrera wrote: > On 2018-Jul-10, Tom Lane wrote: > >> And what about those partition bound values? They are now illegal >> for the domain, so I would expect a dump/reload to fail, regardless >> of whether there are any values in the table. > > Hmm, true. There is a

Re: no partition pruning when partitioning using array type

On 2018/07/11 4:48, Alvaro Herrera wrote: > On 2018-Jul-10, Alvaro Herrera wrote: > >> alvherre=# explain update p set a = a || a where a = '{1}'; >> QUERY PLAN >> ── >> Update on p

Re: no partition pruning when partitioning using array type

On 2018/07/11 3:18, Alvaro Herrera wrote: > On 2018-May-08, Amit Langote wrote: > >> In HEAD, since we already fixed that case in e5dcbb88a15d [1] which is a >> different piece of code anyway, the patch only serves to improve the >> deparse output emitted by ruleutils.c for partition constraint

Re: no partition pruning when partitioning using array type

On 2018-Jul-09, Tom Lane wrote: > Alvaro Herrera writes: > > However, if we take out the > > expression_planner() and replace it with a call to > > strip_implicit_coercions(), not only it magically starts working, but > > also the regression tests start failing with the attached diff, which > >

Re: no partition pruning when partitioning using array type

On 2018-Jul-10, Tom Lane wrote: > And what about those partition bound values? They are now illegal > for the domain, so I would expect a dump/reload to fail, regardless > of whether there are any values in the table. Hmm, true. -- Álvaro Herrerahttps://www.2ndQuadrant.com/

Re: no partition pruning when partitioning using array type

On 2018-Jul-10, Alvaro Herrera wrote: > alvherre=# explain update p set a = a || a where a = '{1}'; > QUERY PLAN > ── > Update on p (cost=0.00..54.03 rows=14 width=38) >Update on p1 >

Re: no partition pruning when partitioning using array type

Alvaro Herrera writes: > On 2018-Jul-09, Tom Lane wrote: >> Suppose you did >> >> create domain overint as int; >> create table pt (a overint) partition by range (a); >> create table pt1 partition of pt for values from (0) to (100); >> >> and the system took it, and then you did >> >> alter

Re: no partition pruning when partitioning using array type

On 2018-May-08, Amit Langote wrote: > In HEAD, since we already fixed that case in e5dcbb88a15d [1] which is a > different piece of code anyway, the patch only serves to improve the > deparse output emitted by ruleutils.c for partition constraint expressions > where pseudo-type partition key is

Re: no partition pruning when partitioning using array type

On 2018-Jul-09, Tom Lane wrote: > Suppose you did > > create domain overint as int; > create table pt (a overint) partition by range (a); > create table pt1 partition of pt for values from (0) to (100); > > and the system took it, and then you did > > alter domain overint add check (value >

Re: no partition pruning when partitioning using array type

Alvaro Herrera writes: > Tracing it down, turns out that transformPartitionBoundValue gets from > coerce_to_target_type a CoerceToDomain node. It then tries to apply > expression_planner() to simplify the expression, but that one doesn't > want anything to do with a domain coercion (for

Re: no partition pruning when partitioning using array type

Another thing I realized when testing this is that partitioning over a domain doesn't work very nicely (tested in 10 and master): create domain overint as int; create table pt (a overint) partition by range (a); create table pt1 partition of pt for values from (0) to (100); results in: ERROR:

Re: no partition pruning when partitioning using array type

Alvaro Herrera writes: > The same occurs in 11 and master. I think this is because the > polymorphic type is resolved for the function ahead of time (at > table creation time); partexprs shows > ({FUNCEXPR :funcid 35757 :funcresulttype 23 :funcretset false :funcvariadic > false :funcformat 0

Re: no partition pruning when partitioning using array type

On 2018-Jul-09, Amit Langote wrote: > On 2018/07/07 9:19, Alvaro Herrera wrote: > > On 2018-May-08, Amit Langote wrote: > > > >> I would like to revisit this as a bug fix for get_partition_operator() to > >> be applied to both PG 10 and HEAD. In the former case, it fixes the bug > >> that

Re: no partition pruning when partitioning using array type

Thanks for taking a look. On 2018/07/07 9:19, Alvaro Herrera wrote: > On 2018-May-08, Amit Langote wrote: > >> I would like to revisit this as a bug fix for get_partition_operator() to >> be applied to both PG 10 and HEAD. In the former case, it fixes the bug >> that constraint exclusion code

Re: no partition pruning when partitioning using array type

On 2018/07/07 0:13, Andrew Dunstan wrote: > > > On 05/08/2018 02:18 AM, Amit Langote wrote: >> On 2018/03/01 17:16, Amit Langote wrote: >>> Added this to CF (actually moved to the September one after first having >>> added it to the CF that is just getting started). >>> >>> It seems to me that

Re: no partition pruning when partitioning using array type

On 2018-May-08, Amit Langote wrote: > I would like to revisit this as a bug fix for get_partition_operator() to > be applied to both PG 10 and HEAD. In the former case, it fixes the bug > that constraint exclusion code will fail to prune correctly when partition > key is of array, enum, range,

Re: no partition pruning when partitioning using array type

On 05/08/2018 02:18 AM, Amit Langote wrote: On 2018/03/01 17:16, Amit Langote wrote: Added this to CF (actually moved to the September one after first having added it to the CF that is just getting started). It seems to me that we don't need to go with my originally proposed approach to

Re: no partition pruning when partitioning using array type

On 2018/03/01 17:16, Amit Langote wrote: > Added this to CF (actually moved to the September one after first having > added it to the CF that is just getting started). > > It seems to me that we don't need to go with my originally proposed > approach to teach predtest.c to strip RelabelType

Re: no partition pruning when partitioning using array type

On 2018/02/02 0:20, Robert Haas wrote: > On Thu, Feb 1, 2018 at 4:42 AM, Amit Langote > wrote: >>> I hope someone else chimes in as well. :) >> >> Bug #15042 [1] seems to be caused by this same problem. There, a >> RelabelType node is being slapped (by the

Re: no partition pruning when partitioning using array type

On Thu, Feb 1, 2018 at 4:42 AM, Amit Langote wrote: >> I hope someone else chimes in as well. :) > > Bug #15042 [1] seems to be caused by this same problem. There, a > RelabelType node is being slapped (by the partitioning code) on a Var node > of a partition key

Re: no partition pruning when partitioning using array type

On 2017/12/11 14:31, Amit Langote wrote: > On 2017/12/09 3:46, Robert Haas wrote: >> On Fri, Dec 8, 2017 at 5:40 AM, Amit Langote wrote: >>> I noticed that if you partition using a array type column, partition >>> pruning using constraint exclusion fails to work due to a minor problem. >> >> I

Re: no partition pruning when partitioning using array type

On 2017/12/09 3:46, Robert Haas wrote: > On Fri, Dec 8, 2017 at 5:40 AM, Amit Langote > wrote: >> I noticed that if you partition using a array type column, partition >> pruning using constraint exclusion fails to work due to a minor problem. >> >> Example: >> >>

Re: no partition pruning when partitioning using array type

On Fri, Dec 8, 2017 at 5:40 AM, Amit Langote wrote: > I noticed that if you partition using a array type column, partition > pruning using constraint exclusion fails to work due to a minor problem. > > Example: > > create table p (a int[]) partition by list (a); >

no partition pruning when partitioning using array type

Hi. I noticed that if you partition using a array type column, partition pruning using constraint exclusion fails to work due to a minor problem. Example: create table p (a int[]) partition by list (a); create table p1 partition of p for values in ('{1}'); create table p1 partition of p for