Re: Declarative partitioning

Hi Ashutosh,

On 2016/06/24 23:08, Ashutosh Bapat wrote:
> Hi Amit,
> I tried creating 2-level partitioned table and tried to create simple table
> using CTAS from the partitioned table. It gives a cache lookup error.
> Here's the test
> CREATE TABLE pt1_l (a int, b varchar, c int) PARTITION BY RANGE(a);
> CREATE TABLE pt1_l_p1 PARTITION OF pt1_l FOR VALUES START (1) END (250)
> INCLUSIVE PARTITION BY RANGE(b);
> CREATE TABLE pt1_l_p2 PARTITION OF pt1_l FOR VALUES START (251) END (500)
> INCLUSIVE PARTITION BY RANGE(((a+c)/2));
> CREATE TABLE pt1_l_p3 PARTITION OF pt1_l FOR VALUES START (501) END (600)
> INCLUSIVE PARTITION BY RANGE(c);
> CREATE TABLE pt1_l_p1_p1 PARTITION OF pt1_l_p1 FOR VALUES START ('000001')
> END ('000125') INCLUSIVE;
> CREATE TABLE pt1_l_p1_p2 PARTITION OF pt1_l_p1 FOR VALUES START ('000126')
> END ('000250') INCLUSIVE;
> CREATE TABLE pt1_l_p2_p1 PARTITION OF pt1_l_p2 FOR VALUES START (251) END
> (375) INCLUSIVE;
> CREATE TABLE pt1_l_p2_p2 PARTITION OF pt1_l_p2 FOR VALUES START (376) END
> (500) INCLUSIVE;
> CREATE TABLE pt1_l_p3_p1 PARTITION OF pt1_l_p3 FOR VALUES START (501) END
> (550) INCLUSIVE;
> CREATE TABLE pt1_l_p3_p2 PARTITION OF pt1_l_p3 FOR VALUES START (551) END
> (600) INCLUSIVE;
> INSERT INTO pt1_l SELECT i, to_char(i, 'FM000000'), i FROM
> generate_series(1, 600, 2) i;
> CREATE TABLE upt1_l AS SELECT * FROM pt1_l;
>
> The last statement gives error "ERROR:  cache lookup failed for function
> 0". Let me know if this problem is reproducible.

Thanks for the test case.  I can reproduce the error.  It has to do with
partition key column (b) being of type varchar whereas the default
operator family for the type being text_ops and there not being an
=(varchar, varchar) operator in text_ops.

When creating a plan for SELECT * FROM pt1_l, which is an Append plan,
partition check quals are generated internally to be used for constraint
exclusion - such as, b >= '000001' AND b < '000125'.  Individual OpExpr's
are generated by using information from PartitionKey of the parent
(PartitionKey) and pg_partition entry of the partition.  When choosing the
operator to use for =, >=, <, etc., opfamily and typid of corresponding
columns are referred.  As mentioned above, in this case, they happened to
be text_ops and varchar, respectively, for column b.  There doesn't exist
an operator =(varchar, varchar) in text_ops, so InvalidOid is returned by
get_opfamily_member which goes unchecked until the error in question occurs.

I have tried to fix this in attached updated patch by using operator class
input type for operator resolution in cases where column type didn't help.

Thanks,
Amit