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

Commit2604a1c

Browse files
committed
introduce new regression test (pathman_update_trigger)
1 parent9e6f32b commit2604a1c

File tree

5 files changed

+377
-58
lines changed

5 files changed

+377
-58
lines changed

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ DATA = pg_pathman--1.0--1.1.sql \
2222
pg_pathman--1.1--1.2.sql\
2323
pg_pathman--1.2--1.3.sql
2424

25-
PGFILEDESC = "pg_pathman - partitioning tool"
25+
PGFILEDESC = "pg_pathman - partitioning tool for PostgreSQL"
2626

2727
REGRESS = pathman_basic\
2828
pathman_only\
@@ -39,6 +39,7 @@ REGRESS = pathman_basic \
3939
pathman_runtime_nodes\
4040
pathman_utility_stmt\
4141
pathman_column_type\
42+
pathman_update_trigger\
4243
pathman_calamity
4344

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

‎expected/pathman_basic.out

Lines changed: 1 addition & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -367,49 +367,6 @@ NOTICE: drop cascades to 8 other objects
367367
SET pg_pathman.enable_runtimeappend = OFF;
368368
SET pg_pathman.enable_runtimemergeappend = OFF;
369369
VACUUM;
370-
/* update triggers test */
371-
SELECT pathman.create_update_triggers('test.hash_rel');
372-
create_update_triggers
373-
------------------------
374-
375-
(1 row)
376-
377-
UPDATE test.hash_rel SET value = 7 WHERE value = 6;
378-
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 7;
379-
QUERY PLAN
380-
------------------------------
381-
Append
382-
-> Seq Scan on hash_rel_1
383-
Filter: (value = 7)
384-
(3 rows)
385-
386-
SELECT * FROM test.hash_rel WHERE value = 7;
387-
id | value
388-
----+-------
389-
6 | 7
390-
(1 row)
391-
392-
SELECT pathman.create_update_triggers('test.num_range_rel');
393-
create_update_triggers
394-
------------------------
395-
396-
(1 row)
397-
398-
UPDATE test.num_range_rel SET id = 3001 WHERE id = 1;
399-
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = 3001;
400-
QUERY PLAN
401-
-----------------------------------
402-
Append
403-
-> Seq Scan on num_range_rel_4
404-
Filter: (id = 3001)
405-
(3 rows)
406-
407-
SELECT * FROM test.num_range_rel WHERE id = 3001;
408-
id | txt
409-
------+----------------------------------
410-
3001 | c4ca4238a0b923820dcc509a6f75849b
411-
(1 row)
412-
413370
SET enable_indexscan = OFF;
414371
SET enable_bitmapscan = OFF;
415372
SET enable_seqscan = ON;
@@ -1275,8 +1232,6 @@ SELECT pathman.replace_hash_partition('test.hash_rel_0', 'test.hash_rel_extern')
12751232
abc | integer | | plain | |
12761233
Indexes:
12771234
"hash_rel_0_pkey" PRIMARY KEY, btree (id)
1278-
Triggers:
1279-
hash_rel_upd_trig BEFORE UPDATE OF value ON test.hash_rel_0 FOR EACH ROW EXECUTE PROCEDURE pathman.pathman_update_trigger_func()
12801235

12811236
\d+ test.hash_rel_extern
12821237
Table "test.hash_rel_extern"
@@ -1346,7 +1301,7 @@ SELECT COUNT(*) FROM ONLY test.hash_rel;
13461301

13471302
DROP TABLE test.hash_rel CASCADE;
13481303
SELECT pathman.drop_partitions('test.num_range_rel');
1349-
NOTICE:998 rows copied from test.num_range_rel_1
1304+
NOTICE:999 rows copied from test.num_range_rel_1
13501305
NOTICE: 1000 rows copied from test.num_range_rel_2
13511306
NOTICE: 1000 rows copied from test.num_range_rel_3
13521307
drop_partitions

‎expected/pathman_update_trigger.out

Lines changed: 239 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,239 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE EXTENSION pg_pathman;
4+
CREATE SCHEMA test_update_trigger;
5+
/* Partition table by RANGE (NUMERIC) */
6+
CREATE TABLE test_update_trigger.test_range(val NUMERIC NOT NULL, comment TEXT);
7+
INSERT INTO test_update_trigger.test_range SELECT i, i FROM generate_series(1, 100) i;
8+
SELECT create_range_partitions('test_update_trigger.test_range', 'val', 1, 10);
9+
NOTICE: sequence "test_range_seq" does not exist, skipping
10+
create_range_partitions
11+
-------------------------
12+
10
13+
(1 row)
14+
15+
SELECT create_update_triggers('test_update_trigger.test_range');
16+
create_update_triggers
17+
------------------------
18+
19+
(1 row)
20+
21+
/* Update values in 1st partition (rows remain there) */
22+
UPDATE test_update_trigger.test_range SET val = 5 WHERE val <= 10;
23+
/* Check values #1 */
24+
SELECT tableoid::REGCLASS, *
25+
FROM test_update_trigger.test_range
26+
WHERE val < 10
27+
ORDER BY comment;
28+
tableoid | val | comment
29+
----------------------------------+-----+---------
30+
test_update_trigger.test_range_1 | 5 | 1
31+
test_update_trigger.test_range_1 | 5 | 10
32+
test_update_trigger.test_range_1 | 5 | 2
33+
test_update_trigger.test_range_1 | 5 | 3
34+
test_update_trigger.test_range_1 | 5 | 4
35+
test_update_trigger.test_range_1 | 5 | 5
36+
test_update_trigger.test_range_1 | 5 | 6
37+
test_update_trigger.test_range_1 | 5 | 7
38+
test_update_trigger.test_range_1 | 5 | 8
39+
test_update_trigger.test_range_1 | 5 | 9
40+
(10 rows)
41+
42+
SELECT count(*) FROM test_update_trigger.test_range;
43+
count
44+
-------
45+
100
46+
(1 row)
47+
48+
/* Update values in 2nd partition (rows move to 3rd partition) */
49+
UPDATE test_update_trigger.test_range SET val = val + 10 WHERE val > 10 AND val <= 20;
50+
/* Check values #2 */
51+
SELECT tableoid::REGCLASS, *
52+
FROM test_update_trigger.test_range
53+
WHERE val > 20 AND val <= 30
54+
ORDER BY comment;
55+
tableoid | val | comment
56+
----------------------------------+-----+---------
57+
test_update_trigger.test_range_3 | 21 | 11
58+
test_update_trigger.test_range_3 | 22 | 12
59+
test_update_trigger.test_range_3 | 23 | 13
60+
test_update_trigger.test_range_3 | 24 | 14
61+
test_update_trigger.test_range_3 | 25 | 15
62+
test_update_trigger.test_range_3 | 26 | 16
63+
test_update_trigger.test_range_3 | 27 | 17
64+
test_update_trigger.test_range_3 | 28 | 18
65+
test_update_trigger.test_range_3 | 29 | 19
66+
test_update_trigger.test_range_3 | 30 | 20
67+
test_update_trigger.test_range_3 | 21 | 21
68+
test_update_trigger.test_range_3 | 22 | 22
69+
test_update_trigger.test_range_3 | 23 | 23
70+
test_update_trigger.test_range_3 | 24 | 24
71+
test_update_trigger.test_range_3 | 25 | 25
72+
test_update_trigger.test_range_3 | 26 | 26
73+
test_update_trigger.test_range_3 | 27 | 27
74+
test_update_trigger.test_range_3 | 28 | 28
75+
test_update_trigger.test_range_3 | 29 | 29
76+
test_update_trigger.test_range_3 | 30 | 30
77+
(20 rows)
78+
79+
SELECT count(*) FROM test_update_trigger.test_range;
80+
count
81+
-------
82+
100
83+
(1 row)
84+
85+
/* Move single row */
86+
UPDATE test_update_trigger.test_range SET val = 90 WHERE val = 80;
87+
/* Check values #3 */
88+
SELECT tableoid::REGCLASS, *
89+
FROM test_update_trigger.test_range
90+
WHERE val = 90
91+
ORDER BY comment;
92+
tableoid | val | comment
93+
----------------------------------+-----+---------
94+
test_update_trigger.test_range_9 | 90 | 80
95+
test_update_trigger.test_range_9 | 90 | 90
96+
(2 rows)
97+
98+
SELECT count(*) FROM test_update_trigger.test_range;
99+
count
100+
-------
101+
100
102+
(1 row)
103+
104+
/* Move single row (create new partition) */
105+
UPDATE test_update_trigger.test_range SET val = -1 WHERE val = 50;
106+
/* Check values #4 */
107+
SELECT tableoid::REGCLASS, *
108+
FROM test_update_trigger.test_range
109+
WHERE val = -1
110+
ORDER BY comment;
111+
tableoid | val | comment
112+
-----------------------------------+-----+---------
113+
test_update_trigger.test_range_11 | -1 | 50
114+
(1 row)
115+
116+
SELECT count(*) FROM test_update_trigger.test_range;
117+
count
118+
-------
119+
100
120+
(1 row)
121+
122+
/* Update non-key column */
123+
UPDATE test_update_trigger.test_range SET comment = 'test!' WHERE val = 100;
124+
/* Check values #5 */
125+
SELECT tableoid::REGCLASS, *
126+
FROM test_update_trigger.test_range
127+
WHERE val = 100
128+
ORDER BY comment;
129+
tableoid | val | comment
130+
-----------------------------------+-----+---------
131+
test_update_trigger.test_range_10 | 100 | test!
132+
(1 row)
133+
134+
SELECT count(*) FROM test_update_trigger.test_range;
135+
count
136+
-------
137+
100
138+
(1 row)
139+
140+
/* Try moving row into a gap (ERROR) */
141+
DROP TABLE test_update_trigger.test_range_4;
142+
UPDATE test_update_trigger.test_range SET val = 35 WHERE val = 70;
143+
ERROR: cannot spawn a partition
144+
/* Check values #6 */
145+
SELECT tableoid::REGCLASS, *
146+
FROM test_update_trigger.test_range
147+
WHERE val = 70
148+
ORDER BY comment;
149+
tableoid | val | comment
150+
----------------------------------+-----+---------
151+
test_update_trigger.test_range_7 | 70 | 70
152+
(1 row)
153+
154+
SELECT count(*) FROM test_update_trigger.test_range;
155+
count
156+
-------
157+
90
158+
(1 row)
159+
160+
/* Test trivial move (same key) */
161+
UPDATE test_update_trigger.test_range SET val = 65 WHERE val = 65;
162+
/* Check values #7 */
163+
SELECT tableoid::REGCLASS, *
164+
FROM test_update_trigger.test_range
165+
WHERE val = 65
166+
ORDER BY comment;
167+
tableoid | val | comment
168+
----------------------------------+-----+---------
169+
test_update_trigger.test_range_7 | 65 | 65
170+
(1 row)
171+
172+
SELECT count(*) FROM test_update_trigger.test_range;
173+
count
174+
-------
175+
90
176+
(1 row)
177+
178+
/* Partition table by HASH (INT4) */
179+
CREATE TABLE test_update_trigger.test_hash(val INT4 NOT NULL, comment TEXT);
180+
INSERT INTO test_update_trigger.test_hash SELECT i, i FROM generate_series(1, 10) i;
181+
SELECT create_hash_partitions('test_update_trigger.test_hash', 'val', 3);
182+
create_hash_partitions
183+
------------------------
184+
3
185+
(1 row)
186+
187+
SELECT create_update_triggers('test_update_trigger.test_hash');
188+
create_update_triggers
189+
------------------------
190+
191+
(1 row)
192+
193+
/* Move all rows into single partition */
194+
UPDATE test_update_trigger.test_hash SET val = 1;
195+
/* Check values #1 */
196+
SELECT tableoid::REGCLASS, *
197+
FROM test_update_trigger.test_hash
198+
WHERE val = 1
199+
ORDER BY comment;
200+
tableoid | val | comment
201+
---------------------------------+-----+---------
202+
test_update_trigger.test_hash_2 | 1 | 1
203+
test_update_trigger.test_hash_2 | 1 | 10
204+
test_update_trigger.test_hash_2 | 1 | 2
205+
test_update_trigger.test_hash_2 | 1 | 3
206+
test_update_trigger.test_hash_2 | 1 | 4
207+
test_update_trigger.test_hash_2 | 1 | 5
208+
test_update_trigger.test_hash_2 | 1 | 6
209+
test_update_trigger.test_hash_2 | 1 | 7
210+
test_update_trigger.test_hash_2 | 1 | 8
211+
test_update_trigger.test_hash_2 | 1 | 9
212+
(10 rows)
213+
214+
SELECT count(*) FROM test_update_trigger.test_hash;
215+
count
216+
-------
217+
10
218+
(1 row)
219+
220+
/* Don't move any rows */
221+
UPDATE test_update_trigger.test_hash SET val = 3 WHERE val = 2;
222+
/* Check values #2 */
223+
SELECT tableoid::REGCLASS, *
224+
FROM test_update_trigger.test_hash
225+
WHERE val = 3
226+
ORDER BY comment;
227+
tableoid | val | comment
228+
----------+-----+---------
229+
(0 rows)
230+
231+
SELECT count(*) FROM test_update_trigger.test_hash;
232+
count
233+
-------
234+
10
235+
(1 row)
236+
237+
DROP SCHEMA test_update_trigger CASCADE;
238+
NOTICE: drop cascades to 16 other objects
239+
DROP EXTENSION pg_pathman;

‎sql/pathman_basic.sql

Lines changed: 0 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -126,17 +126,6 @@ SET pg_pathman.enable_runtimemergeappend = OFF;
126126

127127
VACUUM;
128128

129-
/* update triggers test*/
130-
SELECTpathman.create_update_triggers('test.hash_rel');
131-
UPDATEtest.hash_relSET value=7WHERE value=6;
132-
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=7;
133-
SELECT*FROMtest.hash_relWHERE value=7;
134-
135-
SELECTpathman.create_update_triggers('test.num_range_rel');
136-
UPDATEtest.num_range_relSET id=3001WHERE id=1;
137-
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id=3001;
138-
SELECT*FROMtest.num_range_relWHERE id=3001;
139-
140129
SET enable_indexscan= OFF;
141130
SET enable_bitmapscan= OFF;
142131
SET enable_seqscan=ON;

0 commit comments

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