We have migrated to new Google sites!
Post date: Dec 05, 2012 12:36:18 PM
Why there are duplicate rows in pg_partition_columns? Is it on purpose or some known/unknown bug?
For example, I used this DDL from the admin guide on 4.2.2.4:
CREATE TABLE sales (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (month)
SUBPARTITION TEMPLATE (
START (1) END (13) EVERY (1),
DEFAULT SUBPARTITION other_months )
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES ('usa'),
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION asia VALUES ('asia'),
DEFAULT SUBPARTITION other_regions )
( START (2002) END (2010) EVERY (1),
DEFAULT PARTITION outlying_years );
select *
from pg_partition_columns
where tablename = 'sales';
schemaname tablename columnname partitionlevel position_in_partition_key
public sales year 0.00 1.00
public sales month 1.00 1.00
public sales month 1.00 1.00
public sales region 2.00 1.00
public sales region 2.00
It is because one of the duplicate lines represent subpartition template. If you look at the definition of pg_partition_columns by \d+ pg_partition_columns from psql, it looks like
View "pg_catalog.pg_partition_columns"
Column | Type | Modifiers | Storage | Description
---------------------------+----------+-----------+---------+-------------
schemaname | name | | plain |
tablename | name | | plain |
columnname | name | | plain |
partitionlevel | smallint | | plain |
position_in_partition_key | integer | | plain |
View definition:
SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname AS columnname, p.parlevel AS partitionlevel, p.i + 1 AS position_in_partition_key
FROM pg_namespace n, pg_class c, pg_attribute a, ( SELECT p.parrelid, p.parlevel, p.paratts[i.i] AS attnum, i.i
FROM pg_partition p, generate_series(0, ( SELECT max(array_upper(pg_partition.paratts, 1)) AS max
FROM pg_partition)) i(i)
WHERE p.paratts[i.i] IS NOT NULL) p
WHERE p.parrelid = c.oid AND c.relnamespace = n.oid AND p.attnum = a.attnum AND a.attrelid = c.oid;
And if you add "p.paristemplate = false" after "p.paratts[i.i] IS NOT NULL" in the middle, the result will be what you expect.
This looks to me like an oversight in the view definition in not having paristemplate = false, or not having an extra column that says the row is for template.