Skip to content

Wrong result after drop partition (gap) #117

Closed
@killua001

Description

@killua001

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions