Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions
On Nov 1, 2017 02:41, "Stephen Froehlich"wrote: Hi Michael, So if I'm reading this correctly, the proper way to do my use case is to use partitions of partitions, right? Or maybe reverse the order of the columns: PARTITION BY RANGE (source_no, start_time) --Stephen -Original Message- From: Michael Paquier [mailto:michael.paqu...@gmail.com] Sent: Tuesday, October 31, 2017 4:06 PM To: Stephen Froehlich Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions On Tue, Oct 31, 2017 at 8:36 PM, Stephen Froehlich < s.froehl...@cablelabs.com> wrote: > CREATE TABLE lotsa_data_20171027_src3 PARTITION OF lotsa_data > FOR VALUES FROM ('2017-10-26 18:00:00-06', 3) TO ('2017-10-27 > 17:59:59.999-06', 3); > ERROR: partition " lotsa_data_20171027_src1" would overlap partition > "lotsa_data_20171027_src3" > > Why am I getting this error? The answer is in the documentation: https://www.postgresql.org/docs/devel/static/sql-createtable.html The rules of such partitions is bound to row-wise comparisons. "For example, given PARTITION BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4." So in your example and by comparing with the docs, the first partition allows start_time = '2017-10-26 18:00:00-06' with source_no >= 1, and the second partition allows source_no >= 3 which overlaps with the first one. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions
Hi Michael, So if I'm reading this correctly, the proper way to do my use case is to use partitions of partitions, right? --Stephen -Original Message- From: Michael Paquier [mailto:michael.paqu...@gmail.com] Sent: Tuesday, October 31, 2017 4:06 PM To: Stephen FroehlichCc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions On Tue, Oct 31, 2017 at 8:36 PM, Stephen Froehlich wrote: > CREATE TABLE lotsa_data_20171027_src3 PARTITION OF lotsa_data > FOR VALUES FROM ('2017-10-26 18:00:00-06', 3) TO ('2017-10-27 > 17:59:59.999-06', 3); > ERROR: partition " lotsa_data_20171027_src1" would overlap partition > "lotsa_data_20171027_src3" > > Why am I getting this error? The answer is in the documentation: https://www.postgresql.org/docs/devel/static/sql-createtable.html The rules of such partitions is bound to row-wise comparisons. "For example, given PARTITION BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4." So in your example and by comparing with the docs, the first partition allows start_time = '2017-10-26 18:00:00-06' with source_no >= 1, and the second partition allows source_no >= 3 which overlaps with the first one. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions
On Tue, Oct 31, 2017 at 8:36 PM, Stephen Froehlichwrote: > CREATE TABLE lotsa_data_20171027_src3 PARTITION OF lotsa_data > FOR VALUES FROM ('2017-10-26 18:00:00-06', 3) TO ('2017-10-27 > 17:59:59.999-06', 3); > ERROR: partition " lotsa_data_20171027_src1" would overlap partition > "lotsa_data_20171027_src3" > > Why am I getting this error? The answer is in the documentation: https://www.postgresql.org/docs/devel/static/sql-createtable.html The rules of such partitions is bound to row-wise comparisons. "For example, given PARTITION BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4." So in your example and by comparing with the docs, the first partition allows start_time = '2017-10-26 18:00:00-06' with source_no >= 1, and the second partition allows source_no >= 3 which overlaps with the first one. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general