Обсуждение: [HACKERS] Range Partitioning behaviour - query
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.
Created the following partitioned tables :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
db03=# CREATE TABLE orders_y1992PARTITION OF orders FOR VALUES FROM ('1992-01-01') TO ('1992-12-31'); CREATE TABLEdb03=# CREATE TABLE orders_y1993PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('1993-12-31');CREATE TABLEdb03=# CREATE TABLE orders_y1994PARTITION 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 -
I would want the partition "orders_y1993" to accept all the rows with the date 1993-12-31.db03=# copy orders from '/data/orders.csv' delimiter '|';ERROR: no partition of relation "orders" found for rowDETAIL: Failing row contains (353, 8878, F, 273342, 1993-12-31, 5-LOW , Clerk#000002241, 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#000002241|0| quiet ideas sleep. even instructions..."
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 TABLEdb03=# create table test_part_5 partition of test_part for values from (1) to (5);CREATE TABLEdb03=# 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 rowDETAIL: 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
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#000002241, 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#000002241|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
On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> 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#000002241, 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#000002241|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
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
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.
On 2017/02/24 10:38, David G. Johnston wrote: > On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote <Langote_Amit_f8@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. > 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
On Fri, Feb 24, 2017 at 1:01 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
On 2017/02/24 10:38, David G. Johnston wrote:
> On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote <Langote_Amit_f8@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
On Fri, Feb 24, 2017 at 12:38 PM, David G. Johnston <david.g.johnston@gmail.com> 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