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

Commitc4b543f

Browse files
committed
clean up pathman_expressions tests
1 parentd07dc92 commitc4b543f

File tree

5 files changed

+102
-95
lines changed

5 files changed

+102
-95
lines changed

‎Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ REGRESS = pathman_basic \
3131
pathman_column_type\
3232
pathman_cte\
3333
pathman_domains\
34+
pathman_expressions\
3435
pathman_foreign_keys\
3536
pathman_inserts\
3637
pathman_interval\
@@ -42,8 +43,7 @@ REGRESS = pathman_basic \
4243
pathman_runtime_nodes\
4344
pathman_update_trigger\
4445
pathman_updates\
45-
pathman_utility_stmt\
46-
pathman_expressions
46+
pathman_utility_stmt
4747

4848

4949
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add

‎expected/pathman_expressions.out

Lines changed: 33 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1,55 +1,54 @@
11
\set VERBOSITY terse
22
SET search_path = 'public';
3-
CREATE SCHEMA pathman;
4-
CREATE EXTENSION pg_pathman SCHEMA pathman;
5-
CREATE SCHEMA test;
3+
CREATE EXTENSION pg_pathman;
4+
CREATE SCHEMA test_exprs;
65
/* hash */
7-
CREATE TABLEtest.hash_rel (
6+
CREATE TABLEtest_exprs.hash_rel (
87
idSERIAL PRIMARY KEY,
98
valueINTEGER,
109
value2 INTEGER
1110
);
12-
INSERT INTOtest.hash_rel (value, value2)
11+
INSERT INTOtest_exprs.hash_rel (value, value2)
1312
SELECT val, val * 2 FROM generate_series(1, 5) val;
14-
SELECT COUNT(*) FROMtest.hash_rel;
13+
SELECT COUNT(*) FROMtest_exprs.hash_rel;
1514
count
1615
-------
1716
5
1817
(1 row)
1918

20-
SELECTpathman.create_hash_partitions('test.hash_rel', 'value * value2', 4);
19+
SELECT create_hash_partitions('test_exprs.hash_rel', 'value * value2', 4);
2120
create_hash_partitions
2221
------------------------
2322
4
2423
(1 row)
2524

26-
SELECT COUNT(*) FROM ONLYtest.hash_rel;
25+
SELECT COUNT(*) FROM ONLYtest_exprs.hash_rel;
2726
count
2827
-------
2928
0
3029
(1 row)
3130

32-
SELECT COUNT(*) FROMtest.hash_rel;
31+
SELECT COUNT(*) FROMtest_exprs.hash_rel;
3332
count
3433
-------
3534
5
3635
(1 row)
3736

38-
INSERT INTOtest.hash_rel (value, value2)
37+
INSERT INTOtest_exprs.hash_rel (value, value2)
3938
SELECT val, val * 2 FROM generate_series(6, 10) val;
40-
SELECT COUNT(*) FROM ONLYtest.hash_rel;
39+
SELECT COUNT(*) FROM ONLYtest_exprs.hash_rel;
4140
count
4241
-------
4342
0
4443
(1 row)
4544

46-
SELECT COUNT(*) FROMtest.hash_rel;
45+
SELECT COUNT(*) FROMtest_exprs.hash_rel;
4746
count
4847
-------
4948
10
5049
(1 row)
5150

52-
EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel WHERE value = 5;
51+
EXPLAIN (COSTS OFF) SELECT * FROMtest_exprs.hash_rel WHERE value = 5;
5352
QUERY PLAN
5453
------------------------------
5554
Append
@@ -63,7 +62,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 5;
6362
Filter: (value = 5)
6463
(9 rows)
6564

66-
EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel WHERE (value * value2) = 5;
65+
EXPLAIN (COSTS OFF) SELECT * FROMtest_exprs.hash_rel WHERE (value * value2) = 5;
6766
QUERY PLAN
6867
----------------------------------------
6968
Append
@@ -72,83 +71,86 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE (value * value2) = 5;
7271
(3 rows)
7372

7473
/* range */
75-
CREATE TABLEtest.range_rel (id SERIAL PRIMARY KEY, dt TIMESTAMP, txt TEXT);
76-
INSERT INTOtest.range_rel (dt, txt)
74+
CREATE TABLEtest_exprs.range_rel (id SERIAL PRIMARY KEY, dt TIMESTAMP, txt TEXT);
75+
INSERT INTOtest_exprs.range_rel (dt, txt)
7776
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2020-04-30', '1 month'::interval) as g;
78-
SELECTpathman.create_range_partitions('test.range_rel', 'RANDOM()', '15 years'::INTERVAL, '1 year'::INTERVAL, 10);
77+
SELECT create_range_partitions('test_exprs.range_rel', 'RANDOM()', '15 years'::INTERVAL, '1 year'::INTERVAL, 10);
7978
ERROR: start value is less than min value of "random()"
80-
SELECTpathman.create_range_partitions('test.range_rel', 'AGE(dt, ''2000-01-01''::DATE)',
79+
SELECT create_range_partitions('test_exprs.range_rel', 'AGE(dt, ''2000-01-01''::DATE)',
8180
'15 years'::INTERVAL, '1 year'::INTERVAL, 10);
8281
NOTICE: sequence "range_rel_seq" does not exist, skipping
8382
create_range_partitions
8483
-------------------------
8584
10
8685
(1 row)
8786

88-
INSERT INTOtest.range_rel_1 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
87+
INSERT INTOtest_exprs.range_rel_1 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
8988
ERROR: new row for relation "range_rel_1" violates check constraint "pathman_range_rel_1_check"
90-
SELECT COUNT(*) FROMtest.range_rel_6;
89+
SELECT COUNT(*) FROMtest_exprs.range_rel_6;
9190
count
9291
-------
9392
4
9493
(1 row)
9594

96-
INSERT INTOtest.range_rel_6 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
97-
SELECT COUNT(*) FROMtest.range_rel_6;
95+
INSERT INTOtest_exprs.range_rel_6 (dt, txt) VALUES ('2020-01-01'::DATE, md5('asdf'));
96+
SELECT COUNT(*) FROMtest_exprs.range_rel_6;
9897
count
9998
-------
10099
5
101100
(1 row)
102101

103-
EXPLAIN (COSTS OFF) SELECT * FROMtest.range_rel WHERE (AGE(dt, '2000-01-01'::DATE)) = '18 years'::interval;
102+
EXPLAIN (COSTS OFF) SELECT * FROMtest_exprs.range_rel WHERE (AGE(dt, '2000-01-01'::DATE)) = '18 years'::interval;
104103
QUERY PLAN
105104
-------------------------------------------------------------------------------------------------------------
106105
Append
107106
-> Seq Scan on range_rel_4
108107
Filter: (age(dt, 'Sat Jan 01 00:00:00 2000'::timestamp without time zone) = '@ 18 years'::interval)
109108
(3 rows)
110109

111-
SELECTpathman.create_update_triggers('test.range_rel');
110+
SELECT create_update_triggers('test_exprs.range_rel');
112111
create_update_triggers
113112
------------------------
114113

115114
(1 row)
116115

117-
SELECT COUNT(*) FROMtest.range_rel;
116+
SELECT COUNT(*) FROMtest_exprs.range_rel;
118117
count
119118
-------
120119
65
121120
(1 row)
122121

123-
SELECT COUNT(*) FROMtest.range_rel_1;
122+
SELECT COUNT(*) FROMtest_exprs.range_rel_1;
124123
count
125124
-------
126125
12
127126
(1 row)
128127

129-
SELECT COUNT(*) FROMtest.range_rel_2;
128+
SELECT COUNT(*) FROMtest_exprs.range_rel_2;
130129
count
131130
-------
132131
12
133132
(1 row)
134133

135-
UPDATEtest.range_rel SET dt = '2016-12-01' WHERE dt >= '2015-10-10' AND dt <= '2017-10-10';
134+
UPDATEtest_exprs.range_rel SET dt = '2016-12-01' WHERE dt >= '2015-10-10' AND dt <= '2017-10-10';
136135
/* counts in partitions should be changed */
137-
SELECT COUNT(*) FROMtest.range_rel;
136+
SELECT COUNT(*) FROMtest_exprs.range_rel;
138137
count
139138
-------
140139
65
141140
(1 row)
142141

143-
SELECT COUNT(*) FROMtest.range_rel_1;
142+
SELECT COUNT(*) FROMtest_exprs.range_rel_1;
144143
count
145144
-------
146145
10
147146
(1 row)
148147

149-
SELECT COUNT(*) FROMtest.range_rel_2;
148+
SELECT COUNT(*) FROMtest_exprs.range_rel_2;
150149
count
151150
-------
152151
24
153152
(1 row)
154153

154+
DROP SCHEMA test_exprs CASCADE;
155+
NOTICE: drop cascades to 17 other objects
156+
DROP EXTENSION pg_pathman;

‎expected/pathman_join_clause.out

Lines changed: 15 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -7,17 +7,17 @@ CREATE SCHEMA test;
77
*/
88
/* create test tables */
99
CREATE TABLE test.fk (
10-
id1 INT NOT NULL,
11-
id2 INT NOT NULL,
12-
start_key INT,
13-
end_key INT,
14-
PRIMARY KEY (id1, id2));
10+
id1 INT NOT NULL,
11+
id2 INT NOT NULL,
12+
start_key INT,
13+
end_key INT,
14+
PRIMARY KEY (id1, id2));
1515
CREATE TABLE test.mytbl (
16-
id1 INT NOT NULL,
17-
id2 INT NOT NULL,
18-
key INT NOT NULL,
19-
CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES test.fk(id1, id2),
20-
PRIMARY KEY (id1, key));
16+
id1 INT NOT NULL,
17+
id2 INT NOT NULL,
18+
key INT NOT NULL,
19+
CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES test.fk(id1, id2),
20+
PRIMARY KEY (id1, key));
2121
SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);
2222
create_hash_partitions
2323
------------------------
@@ -26,15 +26,15 @@ SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);
2626

2727
/* ...fill out with test data */
2828
INSERT INTO test.fk VALUES (1, 1);
29-
INSERT INTO test.mytbl VALUES (1, 1, 5), (1,1,6);
29+
INSERT INTO test.mytbl VALUES (1, 1, 5), (1, 1,6);
3030
/* gather statistics on test tables to have deterministic plans */
3131
ANALYZE test.fk;
3232
ANALYZE test.mytbl;
3333
/* run test queries */
3434
EXPLAIN (COSTS OFF) /* test plan */
3535
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
36-
FROM test.mytbl m JOIN test.fk USING(id1, id2)
37-
WHERE NOT key <@ int4range(6, end_key);
36+
FROM test.mytbl m JOIN test.fk USING(id1, id2)
37+
WHERE NOT key <@ int4range(6, end_key);
3838
QUERY PLAN
3939
------------------------------------------------------------------------------------
4040
Nested Loop
@@ -85,8 +85,8 @@ SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
8585

8686
/* test joint data */
8787
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
88-
FROM test.mytbl m JOIN test.fk USING(id1, id2)
89-
WHERE NOT key <@ int4range(6, end_key);
88+
FROM test.mytbl m JOIN test.fk USING(id1, id2)
89+
WHERE NOT key <@ int4range(6, end_key);
9090
tableoid | id1 | id2 | key | start_key | end_key
9191
--------------+-----+-----+-----+-----------+---------
9292
test.mytbl_6 | 1 | 1 | 5 | |

‎sql/pathman_expressions.sql

Lines changed: 36 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1,53 +1,57 @@
11
\set VERBOSITY terse
22

33
SET search_path='public';
4-
CREATESCHEMApathman;
5-
CREATEEXTENSION pg_pathmanSCHEMApathman;
6-
CREATESCHEMAtest;
4+
CREATEEXTENSION pg_pathman;
5+
CREATESCHEMAtest_exprs;
6+
77

88
/* hash*/
9-
CREATETABLEtest.hash_rel (
9+
CREATETABLEtest_exprs.hash_rel (
1010
idSERIALPRIMARY KEY,
1111
valueINTEGER,
1212
value2INTEGER
1313
);
14-
INSERT INTOtest.hash_rel (value, value2)
14+
INSERT INTOtest_exprs.hash_rel (value, value2)
1515
SELECT val, val*2FROM generate_series(1,5) val;
1616

17-
SELECTCOUNT(*)FROMtest.hash_rel;
18-
SELECTpathman.create_hash_partitions('test.hash_rel','value * value2',4);
19-
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
20-
SELECTCOUNT(*)FROMtest.hash_rel;
17+
SELECTCOUNT(*)FROMtest_exprs.hash_rel;
18+
SELECT create_hash_partitions('test_exprs.hash_rel','value * value2',4);
19+
SELECTCOUNT(*)FROM ONLYtest_exprs.hash_rel;
20+
SELECTCOUNT(*)FROMtest_exprs.hash_rel;
2121

22-
INSERT INTOtest.hash_rel (value, value2)
22+
INSERT INTOtest_exprs.hash_rel (value, value2)
2323
SELECT val, val*2FROM generate_series(6,10) val;
24-
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
25-
SELECTCOUNT(*)FROMtest.hash_rel;
24+
SELECTCOUNT(*)FROM ONLYtest_exprs.hash_rel;
25+
SELECTCOUNT(*)FROMtest_exprs.hash_rel;
2626

27-
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=5;
28-
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE (value* value2)=5;
27+
EXPLAIN (COSTS OFF)SELECT*FROMtest_exprs.hash_relWHERE value=5;
28+
EXPLAIN (COSTS OFF)SELECT*FROMtest_exprs.hash_relWHERE (value* value2)=5;
2929

3030
/* range*/
31-
CREATETABLEtest.range_rel (idSERIALPRIMARY KEY, dtTIMESTAMP, txtTEXT);
31+
CREATETABLEtest_exprs.range_rel (idSERIALPRIMARY KEY, dtTIMESTAMP, txtTEXT);
3232

33-
INSERT INTOtest.range_rel (dt, txt)
33+
INSERT INTOtest_exprs.range_rel (dt, txt)
3434
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);
36-
SELECTpathman.create_range_partitions('test.range_rel','AGE(dt,''2000-01-01''::DATE)',
35+
SELECT create_range_partitions('test_exprs.range_rel','RANDOM()','15 years'::INTERVAL,'1 year'::INTERVAL,10);
36+
SELECT create_range_partitions('test_exprs.range_rel','AGE(dt,''2000-01-01''::DATE)',
3737
'15 years'::INTERVAL,'1 year'::INTERVAL,10);
38-
INSERT INTOtest.range_rel_1 (dt, txt)VALUES ('2020-01-01'::DATE, md5('asdf'));
39-
SELECTCOUNT(*)FROMtest.range_rel_6;
40-
INSERT INTOtest.range_rel_6 (dt, txt)VALUES ('2020-01-01'::DATE, md5('asdf'));
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';
38+
INSERT INTOtest_exprs.range_rel_1 (dt, txt)VALUES ('2020-01-01'::DATE, md5('asdf'));
39+
SELECTCOUNT(*)FROMtest_exprs.range_rel_6;
40+
INSERT INTOtest_exprs.range_rel_6 (dt, txt)VALUES ('2020-01-01'::DATE, md5('asdf'));
41+
SELECTCOUNT(*)FROMtest_exprs.range_rel_6;
42+
EXPLAIN (COSTS OFF)SELECT*FROMtest_exprs.range_relWHERE (AGE(dt,'2000-01-01'::DATE))='18 years'::interval;
43+
44+
SELECT create_update_triggers('test_exprs.range_rel');
45+
SELECTCOUNT(*)FROMtest_exprs.range_rel;
46+
SELECTCOUNT(*)FROMtest_exprs.range_rel_1;
47+
SELECTCOUNT(*)FROMtest_exprs.range_rel_2;
48+
UPDATEtest_exprs.range_relSET dt='2016-12-01'WHERE dt>='2015-10-10'AND dt<='2017-10-10';
4949

5050
/* counts in partitions should be changed*/
51-
SELECTCOUNT(*)FROMtest.range_rel;
52-
SELECTCOUNT(*)FROMtest.range_rel_1;
53-
SELECTCOUNT(*)FROMtest.range_rel_2;
51+
SELECTCOUNT(*)FROMtest_exprs.range_rel;
52+
SELECTCOUNT(*)FROMtest_exprs.range_rel_1;
53+
SELECTCOUNT(*)FROMtest_exprs.range_rel_2;
54+
55+
56+
DROPSCHEMA test_exprs CASCADE;
57+
DROP EXTENSION pg_pathman;

‎sql/pathman_join_clause.sql

Lines changed: 16 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -11,22 +11,22 @@ CREATE SCHEMA test;
1111

1212
/* create test tables*/
1313
CREATETABLEtest.fk (
14-
id1INTNOT NULL,
15-
id2INTNOT NULL,
16-
start_keyINT,
17-
end_keyINT,
18-
PRIMARY KEY (id1, id2));
14+
id1INTNOT NULL,
15+
id2INTNOT NULL,
16+
start_keyINT,
17+
end_keyINT,
18+
PRIMARY KEY (id1, id2));
1919
CREATETABLEtest.mytbl (
20-
id1INTNOT NULL,
21-
id2INTNOT NULL,
22-
keyINTNOT NULL,
23-
CONSTRAINT fk_fkFOREIGN KEY (id1, id2)REFERENCEStest.fk(id1, id2),
24-
PRIMARY KEY (id1, key));
20+
id1INTNOT NULL,
21+
id2INTNOT NULL,
22+
keyINTNOT NULL,
23+
CONSTRAINT fk_fkFOREIGN KEY (id1, id2)REFERENCEStest.fk(id1, id2),
24+
PRIMARY KEY (id1, key));
2525
SELECTpathman.create_hash_partitions('test.mytbl','id1',8);
2626

2727
/* ...fill out with test data*/
2828
INSERT INTOtest.fkVALUES (1,1);
29-
INSERT INTOtest.mytblVALUES (1,1,5), (1,1,6);
29+
INSERT INTOtest.mytblVALUES (1,1,5), (1,1,6);
3030

3131
/* gather statistics on test tables to have deterministic plans*/
3232
ANALYZEtest.fk;
@@ -35,12 +35,13 @@ ANALYZE test.mytbl;
3535
/* run test queries*/
3636
EXPLAIN (COSTS OFF)/* test plan*/
3737
SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key
38-
FROMtest.mytbl mJOINtest.fk USING(id1, id2)
39-
WHERE NOT key<@ int4range(6, end_key);
38+
FROMtest.mytbl mJOINtest.fk USING(id1, id2)
39+
WHERE NOT key<@ int4range(6, end_key);
40+
4041
/* test joint data*/
4142
SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key
42-
FROMtest.mytbl mJOINtest.fk USING(id1, id2)
43-
WHERE NOT key<@ int4range(6, end_key);
43+
FROMtest.mytbl mJOINtest.fk USING(id1, id2)
44+
WHERE NOT key<@ int4range(6, end_key);
4445

4546

4647
DROPSCHEMA test CASCADE;

0 commit comments

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