From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
Cc: | Ildar Musin <i(dot)musin(at)postgrespro(dot)ru>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Declarative partitioning |
Date: | 2016-06-27 10:26:13 |
Message-ID: | 81371428-bb4b-1e33-5ad6-8c5c51b52cb7@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
0001-Add-syntax-to-specify-partition-key-v7.patch | text/x-diff | 40.1 KB |
0002-Add-a-IGNORE-dependency-type-v7.patch | text/x-diff | 3.0 KB |
0003-Infrastructure-for-creation-of-partitioned-tables-v7.patch | text/x-diff | 87.4 KB |
0004-Add-syntax-to-create-partitions-v7.patch | text/x-diff | 81.6 KB |
0005-Infrastructure-for-partition-metadata-storage-and-ma-v7.patch | text/x-diff | 103.5 KB |
0006-Introduce-tuple-routing-for-partitioned-tables-v7.patch | text/x-diff | 37.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-06-27 14:13:47 | Broken handling of lwlocknames.h |
Previous Message | Ashutosh Bapat | 2016-06-27 09:56:40 | Re: Postgres_fdw join pushdown - wrong results with whole-row reference |