Duplicate rows in pg_partition_columns

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.