Skip to content

Commit b1d13fd

Browse files
ildusildus
committed
Add basic tests for expressions
1 parent b111fd2 commit b1d13fd

File tree

2 files changed

+143
-0
lines changed

2 files changed

+143
-0
lines changed

‎expected/pathman_expressions.out

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,100 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE SCHEMA pathman;
4+
CREATE EXTENSION pg_pathman SCHEMA pathman;
5+
CREATE SCHEMA test;
6+
/* hash */
7+
CREATE TABLE test.hash_rel (
8+
id SERIAL PRIMARY KEY,
9+
value INTEGER,
10+
value2 INTEGER
11+
);
12+
INSERT INTO test.hash_rel (value, value2)
13+
SELECT val, val * 2 FROM generate_series(1, 5) val;
14+
SELECT COUNT(*) FROM test.hash_rel;
15+
count
16+
-------
17+
5
18+
(1 row)
19+
20+
SELECT pathman.create_hash_partitions('test.hash_rel', 'value * value2', 4);
21+
create_hash_partitions
22+
------------------------
23+
4
24+
(1 row)
25+
26+
SELECT COUNT(*) FROM ONLY test.hash_rel;
27+
count
28+
-------
29+
0
30+
(1 row)
31+
32+
SELECT COUNT(*) FROM test.hash_rel;
33+
count
34+
-------
35+
5
36+
(1 row)
37+
38+
INSERT INTO test.hash_rel (value, value2)
39+
SELECT val, val * 2 FROM generate_series(6, 10) val;
40+
SELECT COUNT(*) FROM ONLY test.hash_rel;
41+
count
42+
-------
43+
0
44+
(1 row)
45+
46+
SELECT COUNT(*) FROM test.hash_rel;
47+
count
48+
-------
49+
10
50+
(1 row)
51+
52+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 5;
53+
QUERY PLAN
54+
------------------------------
55+
Append
56+
-> Seq Scan on hash_rel_0
57+
Filter: (value = 5)
58+
-> Seq Scan on hash_rel_1
59+
Filter: (value = 5)
60+
-> Seq Scan on hash_rel_2
61+
Filter: (value = 5)
62+
-> Seq Scan on hash_rel_3
63+
Filter: (value = 5)
64+
(9 rows)
65+
66+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE (value * value2) = 5;
67+
QUERY PLAN
68+
----------------------------------------
69+
Append
70+
-> Seq Scan on hash_rel_0
71+
Filter: ((value * value2) = 5)
72+
(3 rows)
73+
74+
/* range */
75+
CREATE TABLE test.range_rel (
76+
id SERIAL PRIMARY KEY,
77+
dt TIMESTAMP,
78+
txt TEXT);
79+
INSERT INTO test.range_rel (dt, txt)
80+
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2020-04-30', '1 month'::interval) as g;
81+
SELECT pathman.create_range_partitions('test.range_rel', 'AGE(dt, ''2000-01-01''::DATE)',
82+
'15 years'::INTERVAL, '1 year'::INTERVAL, 10);
83+
NOTICE: sequence "range_rel_seq" does not exist, skipping
84+
create_range_partitions
85+
-------------------------
86+
10
87+
(1 row)
88+
89+
INSERT INTO test.range_rel_1 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
90+
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)
99+
100+
INSERT INTO test.range_rel_6 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));

‎sql/pathman_expressions.sql

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
\set VERBOSITY terse
2+
3+
SET search_path = 'public';
4+
CREATE SCHEMA pathman;
5+
CREATE EXTENSION pg_pathman SCHEMA pathman;
6+
CREATE SCHEMA test;
7+
8+
/* hash */
9+
CREATE TABLE test.hash_rel (
10+
id SERIAL PRIMARY KEY,
11+
value INTEGER,
12+
value2 INTEGER
13+
);
14+
INSERT INTO test.hash_rel (value, value2)
15+
SELECT val, val * 2 FROM generate_series(1, 5) val;
16+
17+
SELECT COUNT(*) FROM test.hash_rel;
18+
SELECT pathman.create_hash_partitions('test.hash_rel', 'value * value2', 4);
19+
SELECT COUNT(*) FROM ONLY test.hash_rel;
20+
SELECT COUNT(*) FROM test.hash_rel;
21+
22+
INSERT INTO test.hash_rel (value, value2)
23+
SELECT val, val * 2 FROM generate_series(6, 10) val;
24+
SELECT COUNT(*) FROM ONLY test.hash_rel;
25+
SELECT COUNT(*) FROM test.hash_rel;
26+
27+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 5;
28+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE (value * value2) = 5;
29+
30+
/* range */
31+
CREATE TABLE test.range_rel (
32+
id SERIAL PRIMARY KEY,
33+
dt TIMESTAMP,
34+
txt TEXT);
35+
36+
INSERT INTO test.range_rel (dt, txt)
37+
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2020-04-30', '1 month'::interval) as g;
38+
SELECT pathman.create_range_partitions('test.range_rel', 'AGE(dt, ''2000-01-01''::DATE)',
39+
'15 years'::INTERVAL, '1 year'::INTERVAL, 10);
40+
INSERT INTO test.range_rel_1 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
41+
SELECT * FROM test.range_rel_6;
42+
INSERT INTO test.range_rel_6 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
43+

0 commit comments

Comments
 (0)