Range Partitioning behaviour - query

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

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

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

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

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)

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

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

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 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)

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