From: | Keith Fiske <keith(at)omniti(dot)com> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | Venkata B Nagothi <nag1010(at)gmail(dot)com>, Amit Langote <amitlangote09(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Erik Rijkers <er(at)xs4all(dot)nl>, Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, pgsql-hackers-owner(at)postgresql(dot)org |
Subject: | Re: Declarative partitioning - another take |
Date: | 2017-01-05 17:16:47 |
Message-ID: | CAG1_KcASki1c8DdOBOdz+WbtrNQfr0Ec640ZNzFW3keRg=_t=w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)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(at)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(at)keith=# insert into measurement (logdate) values ('2016-08-01');
INSERT 0 1
Time: 2.848 ms
keith(at)keith=# select * from measurement_y2016m07;
logdate | peaktemp | unitsales
---------+----------+-----------
(0 rows)
Time: 0.273 ms
keith(at)keith=# select * from measurement_y2016m08;
logdate | peaktemp | unitsales
------------+----------+-----------
2016-08-01 | 1 | «NULL»
(1 row)
Time: 0.272 ms
keith(at)keith=# drop table measurement_y2016m08;
DROP TABLE
Time: 5.919 ms
keith(at)keith=# select * from only measurement;
logdate | peaktemp | unitsales
---------+----------+-----------
(0 rows)
Time: 0.307 ms
keith(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2017-01-05 17:17:26 | Re: Replication/backup defaults |
Previous Message | Andres Freund | 2017-01-05 17:12:49 | Re: Replication/backup defaults |