Skip to content

Commit 03652a7

Browse files
ildusildus
committed
Add tests for expressions
1 parent 55e8949 commit 03652a7

File tree

2 files changed

+82
-18
lines changed

2 files changed

+82
-18
lines changed

‎expected/pathman_expressions.out

Lines changed: 66 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -72,12 +72,11 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE (value * value2) = 5;
7272
(3 rows)
7373

7474
/* range */
75-
CREATE TABLE test.range_rel (
76-
id SERIAL PRIMARY KEY,
77-
dt TIMESTAMP,
78-
txt TEXT);
75+
CREATE TABLE test.range_rel (id SERIAL PRIMARY KEY, dt TIMESTAMP, txt TEXT);
7976
INSERT INTO test.range_rel (dt, txt)
8077
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2020-04-30', '1 month'::interval) as g;
78+
SELECT pathman.create_range_partitions('test.range_rel', 'RANDOM()', '15 years'::INTERVAL, '1 year'::INTERVAL, 10);
79+
ERROR: start value is less than min value of "random()"
8180
SELECT pathman.create_range_partitions('test.range_rel', 'AGE(dt, ''2000-01-01''::DATE)',
8281
'15 years'::INTERVAL, '1 year'::INTERVAL, 10);
8382
NOTICE: sequence "range_rel_seq" does not exist, skipping
@@ -88,13 +87,68 @@ NOTICE: sequence "range_rel_seq" does not exist, skipping
8887

8988
INSERT INTO test.range_rel_1 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
9089
ERROR: new row for relation "range_rel_1" violates check constraint "pathman_range_rel_1_check"
91-
SELECT * FROM test.range_rel_6;
92-
id | dt | txt
93-
----+--------------------------+----------------------------------
94-
61 | Wed Jan 01 00:00:00 2020 | 339e0b1f73322ffca5ec77523ff1adfa
95-
62 | Sat Feb 01 00:00:00 2020 | 3c09dde93bf2730744668c266845a828
96-
63 | Sun Mar 01 00:00:00 2020 | e6c8aaac1e4a1eb6594309a2fd24a5e5
97-
64 | Wed Apr 01 00:00:00 2020 | 8cea991c596b35cc412ad489af424341
98-
(4 rows)
90+
SELECT COUNT(*) FROM test.range_rel_6;
91+
count
92+
-------
93+
4
94+
(1 row)
9995

10096
INSERT INTO test.range_rel_6 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
97+
SELECT COUNT(*) FROM test.range_rel_6;
98+
count
99+
-------
100+
5
101+
(1 row)
102+
103+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (AGE(dt, '2000-01-01'::DATE)) = '18 years'::interval;
104+
QUERY PLAN
105+
-------------------------------------------------------------------------------------------------------------
106+
Append
107+
-> Seq Scan on range_rel_4
108+
Filter: (age(dt, 'Sat Jan 01 00:00:00 2000'::timestamp without time zone) = '@ 18 years'::interval)
109+
(3 rows)
110+
111+
SELECT pathman.create_update_triggers('test.range_rel');
112+
create_update_triggers
113+
------------------------
114+
115+
(1 row)
116+
117+
SELECT COUNT(*) FROM test.range_rel;
118+
count
119+
-------
120+
65
121+
(1 row)
122+
123+
SELECT COUNT(*) FROM test.range_rel_1;
124+
count
125+
-------
126+
12
127+
(1 row)
128+
129+
SELECT COUNT(*) FROM test.range_rel_2;
130+
count
131+
-------
132+
12
133+
(1 row)
134+
135+
UPDATE test.range_rel SET dt = '2016-12-01' WHERE dt >= '2015-10-10' AND dt <= '2017-10-10';
136+
/* counts in partitions should be changed */
137+
SELECT COUNT(*) FROM test.range_rel;
138+
count
139+
-------
140+
65
141+
(1 row)
142+
143+
SELECT COUNT(*) FROM test.range_rel_1;
144+
count
145+
-------
146+
10
147+
(1 row)
148+
149+
SELECT COUNT(*) FROM test.range_rel_2;
150+
count
151+
-------
152+
24
153+
(1 row)
154+

‎sql/pathman_expressions.sql

Lines changed: 16 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -28,16 +28,26 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 5;
2828
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE (value * value2) = 5;
2929

3030
/* range */
31-
CREATE TABLE test.range_rel (
32-
id SERIAL PRIMARY KEY,
33-
dt TIMESTAMP,
34-
txt TEXT);
31+
CREATE TABLE test.range_rel (id SERIAL PRIMARY KEY, dt TIMESTAMP, txt TEXT);
3532

3633
INSERT INTO test.range_rel (dt, txt)
3734
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2020-04-30', '1 month'::interval) as g;
35+
SELECT pathman.create_range_partitions('test.range_rel', 'RANDOM()', '15 years'::INTERVAL, '1 year'::INTERVAL, 10);
3836
SELECT pathman.create_range_partitions('test.range_rel', 'AGE(dt, ''2000-01-01''::DATE)',
3937
'15 years'::INTERVAL, '1 year'::INTERVAL, 10);
4038
INSERT INTO test.range_rel_1 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
41-
SELECT * FROM test.range_rel_6;
39+
SELECT COUNT(*) FROM test.range_rel_6;
4240
INSERT INTO test.range_rel_6 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
43-
41+
SELECT COUNT(*) FROM test.range_rel_6;
42+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (AGE(dt, '2000-01-01'::DATE)) = '18 years'::interval;
43+
44+
SELECT pathman.create_update_triggers('test.range_rel');
45+
SELECT COUNT(*) FROM test.range_rel;
46+
SELECT COUNT(*) FROM test.range_rel_1;
47+
SELECT COUNT(*) FROM test.range_rel_2;
48+
UPDATE test.range_rel SET dt = '2016-12-01' WHERE dt >= '2015-10-10' AND dt <= '2017-10-10';
49+
50+
/* counts in partitions should be changed */
51+
SELECT COUNT(*) FROM test.range_rel;
52+
SELECT COUNT(*) FROM test.range_rel_1;
53+
SELECT COUNT(*) FROM test.range_rel_2;

0 commit comments

Comments
 (0)