Thread: [HACKERS] Same expression more than once in partition key
Hi, When we create a range partitioned table, we cannot use a column more than once in the partition key. postgres=# create table t (i int) partition by range(i,i);ERROR: column "i" appears more than once in partition key However, I can use same expression more than once in partition key. postgres=# create table t (i int) partition by range((i),(i)); CREATE TABLE Although this can be blocked by the attached parch, for example, the following is still possible. postgres=# create table t (i int) partition by range((i),i); CREATE TABLE Can these definition be a problem in the internal of partitioning? If not, we might not need to check column that apears more than once in the partition key. Regards, -- Yugo Nagata <nagata@sraoss.co.jp>
On Fri, 23 Jun 2017 15:57:54 +0900 Yugo Nagata <nagata@sraoss.co.jp> wrote: > Hi, > > When we create a range partitioned table, we cannot use > a column more than once in the partition key. > > postgres=# create table t (i int) partition by range(i,i); > ERROR: column "i" appears more than once in partition key > > However, I can use same expression more than once in partition key. > > postgres=# create table t (i int) partition by range((i),(i)); > CREATE TABLE > > Although this can be blocked by the attached parch, for example, > the following is still possible. I forgot to attach it. > > postgres=# create table t (i int) partition by range((i),i); > CREATE TABLE > > Can these definition be a problem in the internal of partitioning? > If not, we might not need to check column that apears more than once > in the partition key. > > Regards, > > > > -- > Yugo Nagata <nagata@sraoss.co.jp> -- Yugo Nagata <nagata@sraoss.co.jp> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Yugo Nagata <nagata@sraoss.co.jp> writes:
> When we create a range partitioned table, we cannot use
> a column more than once in the partition key.
> postgres=# create table t (i int) partition by range(i,i);
> ERROR: column "i" appears more than once in partition key
AFAIK, that's just a simple test for obvious mistakes; it's not a
property that's essential for correctness.
> However, I can use same expression more than once in partition key.
... and so, I can't get excited about extending it to expressions.
Where would you stop --- for example, are i and (i) equal? What
about (i) and (case when true then i else j end)? In principle,
the system could recognize both those identities, but it seems
silly to expend code on it just for nagware purposes.
regards, tom lane
On Fri, 23 Jun 2017 09:54:17 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Yugo Nagata <nagata@sraoss.co.jp> writes: > > When we create a range partitioned table, we cannot use > > a column more than once in the partition key. > > > postgres=# create table t (i int) partition by range(i,i); > > ERROR: column "i" appears more than once in partition key > > AFAIK, that's just a simple test for obvious mistakes; it's not a > property that's essential for correctness. Thanks. This is what I want to know. > > > However, I can use same expression more than once in partition key. > > ... and so, I can't get excited about extending it to expressions. > Where would you stop --- for example, are i and (i) equal? What > about (i) and (case when true then i else j end)? In principle, > the system could recognize both those identities, but it seems > silly to expend code on it just for nagware purposes. > Agreed. > regards, tom lane -- Yugo Nagata <nagata@sraoss.co.jp>
On 6/23/17 09:54, Tom Lane wrote: >> However, I can use same expression more than once in partition key. > > ... and so, I can't get excited about extending it to expressions. > Where would you stop --- for example, are i and (i) equal? What > about (i) and (case when true then i else j end)? In principle, > the system could recognize both those identities, but it seems > silly to expend code on it just for nagware purposes. We also allow the same column more than once in an index. We probably don't have to be more strict here. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> We also allow the same column more than once in an index. We probably
> don't have to be more strict here.
There actually are valid uses for the same column more than once in
an index, eg if you use a different operator class for each instance.
I find it hard to envision a similar use-case in partitioning though.
regards, tom lane
On 2017/06/24 5:04, Tom Lane wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >> We also allow the same column more than once in an index. We probably >> don't have to be more strict here. > > There actually are valid uses for the same column more than once in > an index, eg if you use a different operator class for each instance. > I find it hard to envision a similar use-case in partitioning though. So, does this mean we don't need to apply Nagata-san's patch for now? As far as the partitioning internals are concerned, I see no harm in allowing a column to appear more than once in the partition key. OTOH, I too don't see a partitioning use-case which would require it. Thanks, Amit
On Fri, Jun 23, 2017 at 4:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >> We also allow the same column more than once in an index. We probably >> don't have to be more strict here. > > There actually are valid uses for the same column more than once in > an index, eg if you use a different operator class for each instance. > I find it hard to envision a similar use-case in partitioning though. Maybe you already realize this, but partitioning, like index creation, allows an opclass to be specified: rhaas=# create table foo (a text, b text) partition by range (a text_ops, b text_pattern_ops); CREATE TABLE I don't really see anybody wanting to do that, but I don't really see anyone wanting to do with an index either. My inclination is to reject this patch as not solving any actual problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company