Skip to content

Commit 2604a1c

Browse files
funbringerfunbringer
committed
introduce new regression test (pathman_update_trigger)
1 parent 9e6f32b commit 2604a1c

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-
SELECT pathman.create_update_triggers('test.hash_rel');
131-
UPDATE test.hash_rel SET value = 7 WHERE value = 6;
132-
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 7;
133-
SELECT * FROM test.hash_rel WHERE value = 7;
134-
135-
SELECT pathman.create_update_triggers('test.num_range_rel');
136-
UPDATE test.num_range_rel SET id = 3001 WHERE id = 1;
137-
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = 3001;
138-
SELECT * FROM test.num_range_rel WHERE id = 3001;
139-
140129
SET enable_indexscan = OFF;
141130
SET enable_bitmapscan = OFF;
142131
SET enable_seqscan = ON;

0 commit comments

Comments
 (0)