Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit03652a7

Browse files
committed
Add tests for expressions
1 parent55e8949 commit03652a7

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-
idSERIAL PRIMARY KEY,
77-
dtTIMESTAMP,
78-
txtTEXT);
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*FROMtest.hash_relWHERE (value* value2)=5;
2929

3030
/* range*/
31-
CREATETABLEtest.range_rel (
32-
idSERIALPRIMARY KEY,
33-
dtTIMESTAMP,
34-
txtTEXT);
31+
CREATETABLEtest.range_rel (idSERIALPRIMARY KEY, dtTIMESTAMP, txtTEXT);
3532

3633
INSERT INTOtest.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+
SELECTpathman.create_range_partitions('test.range_rel','RANDOM()','15 years'::INTERVAL,'1 year'::INTERVAL,10);
3836
SELECTpathman.create_range_partitions('test.range_rel','AGE(dt,''2000-01-01''::DATE)',
3937
'15 years'::INTERVAL,'1 year'::INTERVAL,10);
4038
INSERT INTOtest.range_rel_1 (dt, txt)VALUES ('2020-01-01'::DATE, md5('asdf'));
41-
SELECT*FROMtest.range_rel_6;
39+
SELECTCOUNT(*)FROMtest.range_rel_6;
4240
INSERT INTOtest.range_rel_6 (dt, txt)VALUES ('2020-01-01'::DATE, md5('asdf'));
43-
41+
SELECTCOUNT(*)FROMtest.range_rel_6;
42+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE (AGE(dt,'2000-01-01'::DATE))='18 years'::interval;
43+
44+
SELECTpathman.create_update_triggers('test.range_rel');
45+
SELECTCOUNT(*)FROMtest.range_rel;
46+
SELECTCOUNT(*)FROMtest.range_rel_1;
47+
SELECTCOUNT(*)FROMtest.range_rel_2;
48+
UPDATEtest.range_relSET dt='2016-12-01'WHERE dt>='2015-10-10'AND dt<='2017-10-10';
49+
50+
/* counts in partitions should be changed*/
51+
SELECTCOUNT(*)FROMtest.range_rel;
52+
SELECTCOUNT(*)FROMtest.range_rel_1;
53+
SELECTCOUNT(*)FROMtest.range_rel_2;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp