Re: Declarative partitioning - another take

So testing things out in pg_partman for native sub-partitioning and ran
into what is a bug for me that I know I have to fix, but I'm curious if
this can be prevented in the first place within the native partitioning
code itself. The below shows a sub-partitioning set where the sub-partition
has a constraint range that is outside of the range of its parent. If the
columns were different I could see where this would be allowed, but the
same column is used throughout the levels of sub-partitioning.
Understandable if that may be too complex to check for, but figured I'd
bring it up as something I accidentally ran into in case you see an easy
way to prevent it.

This was encountered as of commit ba61a04bc7fefeee03416d9911eb825c4897c223.

keith(at)keith=# \d+ partman_test.time_taptest_table
Table "partman_test.time_taptest_table"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | integer | | | |
plain | |
col2 | text | | | |
extended | |
col3 | timestamp with time zone | | not null | now() |
plain | |
Partition key: RANGE (col3)
Partitions: partman_test.time_taptest_table_p2015 FOR VALUES FROM
('2015-01-01 00:00:00-05') TO ('2016-01-01 00:00:00-05'),
partman_test.time_taptest_table_p2016 FOR VALUES FROM
('2016-01-01 00:00:00-05') TO ('2017-01-01 00:00:00-05'),
partman_test.time_taptest_table_p2017 FOR VALUES FROM
('2017-01-01 00:00:00-05') TO ('2018-01-01 00:00:00-05'),
partman_test.time_taptest_table_p2018 FOR VALUES FROM
('2018-01-01 00:00:00-05') TO ('2019-01-01 00:00:00-05'),
partman_test.time_taptest_table_p2019 FOR VALUES FROM
('2019-01-01 00:00:00-05') TO ('2020-01-01 00:00:00-05')

keith(at)keith=# \d+ partman_test.time_taptest_table_p2015
Table "partman_test.time_taptest_table_p2015"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | integer | | | |
plain | |
col2 | text | | | |
extended | |
col3 | timestamp with time zone | | not null | now() |
plain | |
Partition of: partman_test.time_taptest_table FOR VALUES FROM ('2015-01-01
00:00:00-05') TO ('2016-01-01 00:00:00-05')
Partition key: RANGE (col3)
Partitions: partman_test.time_taptest_table_p2015_p2016_11 FOR VALUES FROM
('2016-11-01 00:00:00-04') TO ('2016-12-01 00:00:00-05'),
partman_test.time_taptest_table_p2015_p2016_12 FOR VALUES FROM
('2016-12-01 00:00:00-05') TO ('2017-01-01 00:00:00-05'),
partman_test.time_taptest_table_p2015_p2017_01 FOR VALUES FROM
('2017-01-01 00:00:00-05') TO ('2017-02-01 00:00:00-05'),
partman_test.time_taptest_table_p2015_p2017_02 FOR VALUES FROM
('2017-02-01 00:00:00-05') TO ('2017-03-01 00:00:00-05'),
partman_test.time_taptest_table_p2015_p2017_03 FOR VALUES FROM
('2017-03-01 00:00:00-05') TO ('2017-04-01 00:00:00-04')

keith(at)keith=# \d+ partman_test.time_taptest_table_p2015_p2017_03
Table
"partman_test.time_taptest_table_p2015_p2017_03"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | integer | | | |
plain | |
col2 | text | | | |
extended | |
col3 | timestamp with time zone | | not null | now() |
plain | |
Partition of: partman_test.time_taptest_table_p2015 FOR VALUES FROM
('2017-03-01 00:00:00-05') TO ('2017-04-01 00:00:00-04')

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com