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 teach predtest.c to strip RelabelType nodes. Apparently, it's
>> only partition.c that's adding the RelableType node around partition key
>> nodes in such cases. That is, in the case of having an array, record,
>> enum, and range type as the partition key. No other part of the system
>> ends up adding one as far as I can see. Parser's make_op(), for example,
>> that is used to generate an OpExpr for a qual involving the partition key,
>> won't put RelabelType around the partition key node if the operator in
>> question has "pseudo"-types listed as declared types of its left and right
>> arguments.
>>
>> So I revised the patch to drop all the predtest.c changes and instead
>> modify get_partition_operator() to avoid generating RelabelType in such
>> cases. Please find it attached.
> 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, or record type due to the structural
> mismatch between the OpExpr that partitioning code generates and one that
> the parser generates for WHERE clauses involving partition key columns.
>
> 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 involved. The change can be seen in
> the updated test output for create_table test.
>
> Attached are patches for PG 10 and HEAD, which implement a slightly
> different approach to fixing this. Now, instead of passing the partition
> key's type as lefttype and righttype to look up the operator, the operator
> class declared type is passed. Also, we now decide whether to create a
> RelabelType node on top based on whether the partition key's type is
> different from the selected operator's input type, with exception for
> pseudo-type types.
>
> Thanks,
> Amit
>
> [1]
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e5dcbb88a15d

Since this email we have branched off REL_11_STABLE. Do we want to
consider this as a bug fix for Release 11? If so, should we add it to
the open items list?

cheers

andrew

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