Re: [HACKERS] Range Partitioning behaviour - query

[Venkata B Nagothi]

On Fri, Feb 24, 2017 at 12:38 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote <
> langote_amit...@lab.ntt.co.jp> wrote:
>
>> On 2017/02/24 8:38, Venkata B Nagothi wrote:
>> > On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
>> >> Upper bound of a range partition is an exclusive bound.  A note was
>> added
>> >> recently to the CREATE TABLE page to make this clear.
>> >>
>> >> https://www.postgresql.org/docs/devel/static/sql-createtable.html
>> >
>> >
>> > Thanks. Actually, my confusion was that the upper bound value would be
>> > included when "TO" clause is used in the syntax.
>>
>> Hmm, TO sounds like it implies inclusive.
>>
>
> ​I think most common usage of the word ends up being inclusive but the
> word itself doesn't really care.​
>
> Dictionary.com has a good example:
>
> "We work from nine to five." - you leave at the beginning of the 5 o'clock
> hour (I'm going for casual usage here)
>

True.


> Since our implementation of ranges is half-open the usage here is
> consistent with that concept.  That it doesn't match BETWEEN is actually
> somewhat nice since you can use ranges for half-open and BETWEEN if you
> want to be concise with fully-closed endpoints.  But it is one more thing
> to remember.
>

Agreed.

Regards,

Venkata B N
Database Consultant

Re: [HACKERS] Range Partitioning behaviour - query

[Venkata B Nagothi]

On Fri, Feb 24, 2017 at 1:01 PM, Amit Langote  wrote:

> On 2017/02/24 10:38, David G. Johnston wrote:
> > On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote <
> langote_amit...@lab.ntt.co.jp
> >> wrote:
> >
> >> On 2017/02/24 8:38, Venkata B Nagothi wrote:
> >>> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
>  Upper bound of a range partition is an exclusive bound.  A note was
> >> added
>  recently to the CREATE TABLE page to make this clear.
> 
>  https://www.postgresql.org/docs/devel/static/sql-createtable.html
> >>>
> >>>
> >>> Thanks. Actually, my confusion was that the upper bound value would be
> >>> included when "TO" clause is used in the syntax.
> >>
> >> Hmm, TO sounds like it implies inclusive.
> >>
> >
> > ​I think most common usage of the word ends up being inclusive but the
> word
> > itself doesn't really care.​
> >
> > Dictionary.com has a good example:
> >
> > "We work from nine to five." - you leave at the beginning of the 5
> o'clock
> > hour (I'm going for casual usage here)
>
> Thanks for that example.
>
> One problem I've seen people mention is one of cognitive dissonance of
> having to define partition_y2013 as FROM ('2013-01-01') TO ('2014-01-01'),
> given that that's the only way to get what one needs. But we concluded
> that that's a reasonable compromise.
>

Agreed. I do see the similar approach adopted across other traditional
RDBMS products as well.

Regards,

Venkata B N
Database Consultant

Re: [HACKERS] Range Partitioning behaviour - query

[Amit Langote]

On 2017/02/24 10:38, David G. Johnston wrote:
> On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote > wrote:
> 
>> On 2017/02/24 8:38, Venkata B Nagothi wrote:
>>> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
 Upper bound of a range partition is an exclusive bound.  A note was
>> added
 recently to the CREATE TABLE page to make this clear.

 https://www.postgresql.org/docs/devel/static/sql-createtable.html
>>>
>>>
>>> Thanks. Actually, my confusion was that the upper bound value would be
>>> included when "TO" clause is used in the syntax.
>>
>> Hmm, TO sounds like it implies inclusive.
>>
> 
> ​I think most common usage of the word ends up being inclusive but the word
> itself doesn't really care.​
> 
> Dictionary.com has a good example:
> 
> "We work from nine to five." - you leave at the beginning of the 5 o'clock
> hour (I'm going for casual usage here)

Thanks for that example.

One problem I've seen people mention is one of cognitive dissonance of
having to define partition_y2013 as FROM ('2013-01-01') TO ('2014-01-01'),
given that that's the only way to get what one needs. But we concluded
that that's a reasonable compromise.

> Since our implementation of ranges is half-open the usage here is
> consistent with that concept.  That it doesn't match BETWEEN is actually
> somewhat nice since you can use ranges for half-open and BETWEEN if you
> want to be concise with fully-closed endpoints.  But it is one more thing
> to remember.

Agreed.

Thanks,
Amit




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Range Partitioning behaviour - query

[David G. Johnston]

On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote  wrote:

> On 2017/02/24 8:38, Venkata B Nagothi wrote:
> > On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
> >> Upper bound of a range partition is an exclusive bound.  A note was
> added
> >> recently to the CREATE TABLE page to make this clear.
> >>
> >> https://www.postgresql.org/docs/devel/static/sql-createtable.html
> >
> >
> > Thanks. Actually, my confusion was that the upper bound value would be
> > included when "TO" clause is used in the syntax.
>
> Hmm, TO sounds like it implies inclusive.
>

​I think most common usage of the word ends up being inclusive but the word
itself doesn't really care.​

Dictionary.com has a good example:

"We work from nine to five." - you leave at the beginning of the 5 o'clock
hour (I'm going for casual usage here)

Since our implementation of ranges is half-open the usage here is
consistent with that concept.  That it doesn't match BETWEEN is actually
somewhat nice since you can use ranges for half-open and BETWEEN if you
want to be concise with fully-closed endpoints.  But it is one more thing
to remember.

David J.

Re: [HACKERS] Range Partitioning behaviour - query

[Amit Langote]

On 2017/02/24 8:38, Venkata B Nagothi wrote:
> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote:
>> Upper bound of a range partition is an exclusive bound.  A note was added
>> recently to the CREATE TABLE page to make this clear.
>>
>> https://www.postgresql.org/docs/devel/static/sql-createtable.html
> 
> 
> Thanks. Actually, my confusion was that the upper bound value would be
> included when "TO" clause is used in the syntax.

Hmm, TO sounds like it implies inclusive.

> Also, there are no options like "<" or "LESS THAN" clauses available. So,
> "TO" translates to "<". That is what i wanted to confirm.

Yes, that's it.

Thanks,
Amit




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Range Partitioning behaviour - query

[Venkata B Nagothi]

On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote  wrote:

> Hi,
>
> On 2017/02/23 11:55, Venkata B Nagothi wrote:
> > Hi Hackers,
> >
> > I have noticed the following behaviour in range partitioning which i felt
> > is not quite correct (i missed reporting this) -
> >
> > I have tested by creating a date ranged partition.
> >
> > I created the following table.
> >
> > db03=# CREATE TABLE orders (
> > o_orderkey INTEGER,
> > o_custkey INTEGER,
> > o_orderstatus CHAR(1),
> > o_totalprice REAL,
> > o_orderdate DATE,
> > o_orderpriority CHAR(15),
> > o_clerk CHAR(15),
> > o_shippriority INTEGER,
> > o_comment VARCHAR(79)) partition by range (o_orderdate);
> > CREATE TABLE
> >
> > Created the following partitioned tables :
> >
> >
> > db03=# CREATE TABLE orders_y1992
> > PARTITION OF orders FOR VALUES FROM ('1992-01-01') TO ('1992-12-31');
> >   CREATE TABLE
> >
> > db03=# CREATE TABLE orders_y1993
> > PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('*1993-12-31
> '*);
> > CREATE TABLE
> >
> > db03=# CREATE TABLE orders_y1994
> >PARTITION OF orders FOR VALUES FROM ('1994-01-01') TO ('1994-12-31');
> > CREATE TABLE
> >
> >
> > The rows with the date "1993-12-31" gets rejected as shown below -
> >
> > db03=# copy orders from '/data/orders.csv' delimiter '|';
> > ERROR:  no partition of relation "orders" found for row
> > DETAIL:  Failing row contains (353, 8878, F, 273342, *1993-12-31*, 5-LOW
> >, Clerk#02241, 0,  quiet ideas sleep. even instructions cajole
> > slyly. silently spe).
> > CONTEXT:  COPY orders, line 89: "353|8878|F|273342|*1993-12-31*|5-LOW
> >|Clerk#02241|0| quiet ideas sleep. even instructions..."
> >
> > I would want the partition "orders_y1993" to accept all the rows with the
> > date 1993-12-31.
>
> [ ... ]
>
> > Am i missing anything here ?
>
> Upper bound of a range partition is an exclusive bound.  A note was added
> recently to the CREATE TABLE page to make this clear.
>
> https://www.postgresql.org/docs/devel/static/sql-createtable.html


Thanks. Actually, my confusion was that the upper bound value would be
included when "TO" clause is used in the syntax.
Also, there are no options like "<" or "LESS THAN" clauses available. So,
"TO" translates to "<". That is what i wanted to confirm.

Regards,

Venkata B N
Database Consultant

Re: [HACKERS] Range Partitioning behaviour - query

[Amit Langote]

Hi,

On 2017/02/23 11:55, Venkata B Nagothi wrote:
> Hi Hackers,
> 
> I have noticed the following behaviour in range partitioning which i felt
> is not quite correct (i missed reporting this) -
> 
> I have tested by creating a date ranged partition.
> 
> I created the following table.
> 
> db03=# CREATE TABLE orders (
> o_orderkey INTEGER,
> o_custkey INTEGER,
> o_orderstatus CHAR(1),
> o_totalprice REAL,
> o_orderdate DATE,
> o_orderpriority CHAR(15),
> o_clerk CHAR(15),
> o_shippriority INTEGER,
> o_comment VARCHAR(79)) partition by range (o_orderdate);
> CREATE TABLE
> 
> Created the following partitioned tables :
> 
> 
> db03=# CREATE TABLE orders_y1992
> PARTITION OF orders FOR VALUES FROM ('1992-01-01') TO ('1992-12-31');
>   CREATE TABLE
> 
> db03=# CREATE TABLE orders_y1993
> PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('*1993-12-31'*);
> CREATE TABLE
> 
> db03=# CREATE TABLE orders_y1994
>PARTITION OF orders FOR VALUES FROM ('1994-01-01') TO ('1994-12-31');
> CREATE TABLE
> 
> 
> The rows with the date "1993-12-31" gets rejected as shown below -
> 
> db03=# copy orders from '/data/orders.csv' delimiter '|';
> ERROR:  no partition of relation "orders" found for row
> DETAIL:  Failing row contains (353, 8878, F, 273342, *1993-12-31*, 5-LOW
>, Clerk#02241, 0,  quiet ideas sleep. even instructions cajole
> slyly. silently spe).
> CONTEXT:  COPY orders, line 89: "353|8878|F|273342|*1993-12-31*|5-LOW
>|Clerk#02241|0| quiet ideas sleep. even instructions..."
> 
> I would want the partition "orders_y1993" to accept all the rows with the
> date 1993-12-31.

[ ... ]

> Am i missing anything here ?

Upper bound of a range partition is an exclusive bound.  A note was added
recently to the CREATE TABLE page to make this clear.

https://www.postgresql.org/docs/devel/static/sql-createtable.html

So do the following instead:

CREATE TABLE orders_y1993
  PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('1994-01-01');

Thanks,
Amit




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Range Partitioning behaviour - query

[Venkata B Nagothi]

Hi Hackers,

I have noticed the following behaviour in range partitioning which i felt
is not quite correct (i missed reporting this) -

I have tested by creating a date ranged partition.

I created the following table.

db03=# CREATE TABLE orders (
o_orderkey INTEGER,
o_custkey INTEGER,
o_orderstatus CHAR(1),
o_totalprice REAL,
o_orderdate DATE,
o_orderpriority CHAR(15),
o_clerk CHAR(15),
o_shippriority INTEGER,
o_comment VARCHAR(79)) partition by range (o_orderdate);
CREATE TABLE

Created the following partitioned tables :


db03=# CREATE TABLE orders_y1992
PARTITION OF orders FOR VALUES FROM ('1992-01-01') TO ('1992-12-31');
  CREATE TABLE

db03=# CREATE TABLE orders_y1993
PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('*1993-12-31'*);
CREATE TABLE

db03=# CREATE TABLE orders_y1994
   PARTITION OF orders FOR VALUES FROM ('1994-01-01') TO ('1994-12-31');
CREATE TABLE


The rows with the date "1993-12-31" gets rejected as shown below -

db03=# copy orders from '/data/orders.csv' delimiter '|';
ERROR:  no partition of relation "orders" found for row
DETAIL:  Failing row contains (353, 8878, F, 273342, *1993-12-31*, 5-LOW
   , Clerk#02241, 0,  quiet ideas sleep. even instructions cajole
slyly. silently spe).
CONTEXT:  COPY orders, line 89: "353|8878|F|273342|*1993-12-31*|5-LOW
   |Clerk#02241|0| quiet ideas sleep. even instructions..."

I would want the partition "orders_y1993" to accept all the rows with the
date 1993-12-31.


To confirm this behaviour, I did another simple test with numbers -


I created two partitioned tables with range values from 1 to 5 and from 6
to 10 as shown below -


db03=# create table test_part ( col int) partition by range (col);
CREATE TABLE
db03=# create table test_part_5 partition of test_part for values from (1)
to (5);
CREATE TABLE
db03=# create table test_part_10 partition of test_part for values from (6)
to (10);
CREATE TABLE


When i try to insert value 5, it gets rejected as shown below

db03=# insert into test_part values (5);
ERROR:  no partition of relation "test_part" found for row
DETAIL:  Failing row contains (5).


The table partition "test_part_5" is not supposed to accept value 5 ?

Am i missing anything here ?

Regards,

Venkata B N
Database Consultant