Re: [HACKERS] Declarative partitioning - another take - Mailing list pgsql-hackers
From | Keith Fiske |
---|---|
Subject | Re: [HACKERS] Declarative partitioning - another take |
Date | |
Msg-id | CAG1_KcASki1c8DdOBOdz+WbtrNQfr0Ec640ZNzFW3keRg=_t=w@mail.gmail.com Whole thread Raw |
In response to | Re: [HACKERS] Declarative partitioning - another take (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Responses |
Re: [HACKERS] Declarative partitioning - another take
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
List | pgsql-hackers |
Could we get some clarification on the partition_bound_spec portion of the PARTITION OF clause? Just doing some testing it seems it's inclusive of the FROM value but exclusive of the TO value. I don't see mention of this in the docs as of commit 18fc5192a631441a73e6a3b911ecb14765140389 yesterday. It does mention that the values aren't allowed to overlap, but looking at the schema below, without the clarification of which side is inclusive/exclusive it seems confusing because 2016-08-01 is in both. Even the child table does not clarify this. Not sure if there's a way to do this in the \d+ display which would be ideal, but it should at least be mentioned in the docs.
keith@keith=# \d+ measurement
Table "public.measurement"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
logdate | date | | not null | | plain | |
peaktemp | integer | | | 1 | plain | |
unitsales | integer | | | | plain | |
Partition key: RANGE (logdate)
Check constraints:
"measurement_peaktemp_check" CHECK (peaktemp > 0)
Partitions: measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'),
measurement_y2016m08 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01')
keith@keith=# \d+ measurement_y2016m07
Table "public.measurement_y2016m07"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
logdate | date | | not null | | plain | |
peaktemp | integer | | | 1 | plain | |
unitsales | integer | | | 0 | plain | |
Partition of: measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
Check constraints:
"measurement_peaktemp_check" CHECK (peaktemp > 0)
keith@keith=# insert into measurement (logdate) values ('2016-08-01');
INSERT 0 1
Time: 2.848 ms
keith@keith=# select * from measurement_y2016m07;
logdate | peaktemp | unitsales
---------+----------+-----------
(0 rows)
Time: 0.273 ms
keith@keith=# select * from measurement_y2016m08;
logdate | peaktemp | unitsales
------------+----------+-----------
2016-08-01 | 1 | «NULL»
(1 row)
Time: 0.272 ms
keith@keith=# drop table measurement_y2016m08;
DROP TABLE
Time: 5.919 ms
keith@keith=# select * from only measurement;
logdate | peaktemp | unitsales
---------+----------+-----------
(0 rows)
Time: 0.307 ms
keith@keith=# insert into measurement (logdate) values ('2016-08-01');
ERROR: no partition of relation "measurement" found for row
DETAIL: Failing row contains (2016-08-01, 1, null).
Time: 0.622 ms
pgsql-hackers by date: