Closed
Description
step
1. create a range partition table with 5 min interval. Then fill data into the table.
CREATE TABLE journal (
id SERIAL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
SELECT create_range_partitions('journal', 'dt', '2017-01-01'::timestamp, '5 min'::interval, 1000);
INSERT INTO journal (dt, level, msg)
SELECT g, random() * 6, md5(g::text)
FROM generate_series('2017-01-01'::timestamp, '2017-01-5'::timestamp, '2 second') as g;
2. drop some partitions for a day's begining
[postgres@cnsz033302:5433/k1 M] [08-28.19:22:37]=# select drop_range_partition('journal_577',true);
drop_range_partition
----------------------
journal_577
(1 row)
Time: 23.144 ms
[postgres@cnsz033302:5433/k1 M] [08-28.19:22:50]=# select drop_range_partition('journal_578',true);
drop_range_partition
----------------------
journal_578
(1 row)
Time: 24.799 ms
[postgres@cnsz033302:5433/k1 M] [08-28.19:22:59]=# select drop_range_partition('journal_579',true);
drop_range_partition
----------------------
journal_579
(1 row)
Time: 18.375 ms
3. wrong result when filter condition contains the droped time range
[postgres@cnsz033302:5433/k1 M] [08-28.19:23:06]=# select count(*) from journal where dt > '2017-01-03'::timestamp;
count
-------
0
(1 row)
Time: 21.987 ms
[postgres@cnsz033302:5433/k1 M] [08-28.19:31:59]=# select count(*) from journal where dt < '2017-01-03 00:05:00'::timestamp;
count
-------
0
(1 row)
Time: 0.535 ms
4. correct result
[postgres@cnsz033302:5433/k1 M] [08-28.19:23:47]=# select count(*) from journal where dt > '2017-01-04'::timestamp;
count
-------
43200
(1 row)
Time: 23.115 ms
[postgres@cnsz033302:5433/k1 M] [08-28.19:23:53]=# select count(*) from journal where dt < '2017-01-03'::timestamp;
count
-------
86400
(1 row)
Time: 47.868 ms
[postgres@cnsz033302:5433/k1 M] [08-28.19:31:43]=# select count(*) from journal where dt < '2017-01-03 05:00'::timestamp;
count
-------
94950
(1 row)
Time: 35.960 ms