Skip to content

Commit 02a4044

Browse files
vbwagnervbwagner
committed
Merge branch 'pathman_pgpro9_5' of gitlab.postgrespro.ru:pgpro-dev/postgrespro into pathman_pgpro9_5
2 parents 3a04310 + 24c39fd commit 02a4044

File tree

6 files changed

+200
-192
lines changed

6 files changed

+200
-192
lines changed

‎contrib/pathman/expected/pathman.out

Lines changed: 45 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -1,51 +1,50 @@
11
\set VERBOSITY terse
2+
CREATE SCHEMA test;
23
CREATE EXTENSION pathman;
3-
CREATE TABLE hash_rel (
4+
CREATE TABLE test.hash_rel (
45
id SERIAL PRIMARY KEY,
56
value INTEGER);
6-
SELECT create_hash_partitions('hash_rel', 'value', 3);
7-
NOTICE: trigger "hash_rel_insert_trigger" for relation "hash_rel" does not exist, skipping
8-
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
9-
NOTICE: trigger "hash_rel_update_trigger" for relation "hash_rel" does not exist, skipping
10-
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
7+
SELECT create_hash_partitions('test.hash_rel', 'value', 3);
8+
NOTICE: trigger "test_hash_rel_insert_trigger" for relation "test.hash_rel" does not exist, skipping
9+
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
1110
create_hash_partitions
1211
------------------------
1312

1413
(1 row)
1514

16-
CREATE TABLE range_rel (
15+
CREATE TABLE test.range_rel (
1716
id SERIAL PRIMARY KEY,
1817
dt TIMESTAMP,
1918
txt TEXT);
20-
SELECT create_range_partitions('range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 3);
19+
SELECT create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 3);
2120
NOTICE: sequence "range_rel_seq" does not exist, skipping
2221
create_range_partitions
2322
-------------------------
2423

2524
(1 row)
2625

27-
CREATE TABLE num_range_rel (
26+
CREATE TABLE test.num_range_rel (
2827
id SERIAL PRIMARY KEY,
2928
txt TEXT);
30-
SELECT create_range_partitions('num_range_rel', 'id', 0, 1000, 3);
29+
SELECT create_range_partitions('test.num_range_rel', 'id', 0, 1000, 3);
3130
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
3231
create_range_partitions
3332
-------------------------
3433

3534
(1 row)
3635

37-
INSERT INTO hash_rel VALUES (1, 1);
38-
INSERT INTO hash_rel VALUES (2, 2);
39-
INSERT INTO hash_rel VALUES (3, 3);
40-
INSERT INTO hash_rel VALUES (4, 4);
41-
INSERT INTO hash_rel VALUES (5, 5);
42-
INSERT INTO hash_rel VALUES (6, 6);
43-
INSERT INTO num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
36+
INSERT INTO test.hash_rel VALUES (1, 1);
37+
INSERT INTO test.hash_rel VALUES (2, 2);
38+
INSERT INTO test.hash_rel VALUES (3, 3);
39+
INSERT INTO test.hash_rel VALUES (4, 4);
40+
INSERT INTO test.hash_rel VALUES (5, 5);
41+
INSERT INTO test.hash_rel VALUES (6, 6);
42+
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
4443
VACUUM;
4544
SET enable_indexscan = OFF;
4645
SET enable_bitmapscan = OFF;
4746
SET enable_seqscan = ON;
48-
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;
47+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
4948
QUERY PLAN
5049
------------------------------
5150
Append
@@ -54,15 +53,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;
5453
-> Seq Scan on hash_rel_2
5554
(4 rows)
5655

57-
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2;
56+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
5857
QUERY PLAN
5958
------------------------------
6059
Append
6160
-> Seq Scan on hash_rel_2
6261
Filter: (value = 2)
6362
(3 rows)
6463

65-
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;
64+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
6665
QUERY PLAN
6766
------------------------------
6867
Append
@@ -72,7 +71,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;
7271
Filter: (value = 2)
7372
(5 rows)
7473

75-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;
74+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
7675
QUERY PLAN
7776
-----------------------------------
7877
Append
@@ -81,15 +80,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;
8180
-> Seq Scan on num_range_rel_4
8281
(4 rows)
8382

84-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1000 AND id < 3000;
83+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
8584
QUERY PLAN
8685
-----------------------------------
8786
Append
8887
-> Seq Scan on num_range_rel_2
8988
-> Seq Scan on num_range_rel_3
9089
(3 rows)
9190

92-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;
91+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
9392
QUERY PLAN
9493
-----------------------------------
9594
Append
@@ -99,7 +98,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;
9998
Filter: (id < 2500)
10099
(5 rows)
101100

102-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
101+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
103102
QUERY PLAN
104103
-----------------------------------
105104
Append
@@ -115,7 +114,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500)
115114
SET enable_indexscan = ON;
116115
SET enable_bitmapscan = OFF;
117116
SET enable_seqscan = OFF;
118-
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;
117+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
119118
QUERY PLAN
120119
------------------------------
121120
Append
@@ -124,15 +123,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;
124123
-> Seq Scan on hash_rel_2
125124
(4 rows)
126125

127-
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2;
126+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
128127
QUERY PLAN
129128
------------------------------
130129
Append
131130
-> Seq Scan on hash_rel_2
132131
Filter: (value = 2)
133132
(3 rows)
134133

135-
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;
134+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
136135
QUERY PLAN
137136
------------------------------
138137
Append
@@ -142,7 +141,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;
142141
Filter: (value = 2)
143142
(5 rows)
144143

145-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;
144+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
146145
QUERY PLAN
147146
----------------------------------------------------------------
148147
Append
@@ -151,15 +150,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;
151150
-> Seq Scan on num_range_rel_4
152151
(4 rows)
153152

154-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1000 AND id < 3000;
153+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
155154
QUERY PLAN
156155
-----------------------------------
157156
Append
158157
-> Seq Scan on num_range_rel_2
159158
-> Seq Scan on num_range_rel_3
160159
(3 rows)
161160

162-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;
161+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
163162
QUERY PLAN
164163
----------------------------------------------------------------
165164
Append
@@ -169,7 +168,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;
169168
Index Cond: (id < 2500)
170169
(5 rows)
171170

172-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
171+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
173172
QUERY PLAN
174173
----------------------------------------------------------------
175174
Append
@@ -186,7 +185,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500)
186185
* Test split and merge
187186
*/
188187
/* Split first partition in half */
189-
SELECT split_range_partition('num_range_rel_1', 500);
188+
SELECT split_range_partition('test.num_range_rel_1', 500);
190189
NOTICE: Creating new partition...
191190
NOTICE: Copying data to new partition...
192191
NOTICE: Altering original partition...
@@ -196,7 +195,7 @@ NOTICE: Done!
196195
{0,1000}
197196
(1 row)
198197

199-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;
198+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id BETWEEN 100 AND 700;
200199
QUERY PLAN
201200
----------------------------------------------------------------
202201
Append
@@ -206,7 +205,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;
206205
Index Cond: (id <= 700)
207206
(5 rows)
208207

209-
SELECT split_range_partition('range_rel_1', '2015-01-15'::DATE);
208+
SELECT split_range_partition('test.range_rel_1', '2015-01-15'::DATE);
210209
NOTICE: Creating new partition...
211210
NOTICE: Copying data to new partition...
212211
NOTICE: Altering original partition...
@@ -217,7 +216,7 @@ NOTICE: Done!
217216
(1 row)
218217

219218
/* Merge two partitions into one */
220-
SELECT merge_range_partitions('num_range_rel_1', 'num_range_rel_' || currval('num_range_rel_seq'));
219+
SELECT merge_range_partitions('test.num_range_rel_1', 'test.num_range_rel_' || currval('test.num_range_rel_seq'));
221220
NOTICE: Altering first partition...
222221
NOTICE: Copying data...
223222
NOTICE: Dropping second partition...
@@ -227,15 +226,15 @@ NOTICE: Done!
227226

228227
(1 row)
229228

230-
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;
229+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id BETWEEN 100 AND 700;
231230
QUERY PLAN
232231
----------------------------------------------------------------
233232
Append
234233
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
235234
Index Cond: ((id >= 100) AND (id <= 700))
236235
(3 rows)
237236

238-
SELECT merge_range_partitions('range_rel_1', 'range_rel_' || currval('range_rel_seq'));
237+
SELECT merge_range_partitions('test.range_rel_1', 'test.range_rel_' || currval('test.range_rel_seq'));
239238
NOTICE: Altering first partition...
240239
NOTICE: Copying data...
241240
NOTICE: Dropping second partition...
@@ -246,31 +245,31 @@ NOTICE: Done!
246245
(1 row)
247246

248247
/* Append and prepend partitions */
249-
SELECT append_partition('num_range_rel');
248+
SELECT append_partition('test.num_range_rel');
250249
NOTICE: Appending new partition...
251250
NOTICE: Done!
252251
append_partition
253252
------------------
254253

255254
(1 row)
256255

257-
SELECT prepend_partition('num_range_rel');
256+
SELECT prepend_partition('test.num_range_rel');
258257
NOTICE: Prepending new partition...
259258
NOTICE: Done!
260259
prepend_partition
261260
-------------------
262261

263262
(1 row)
264263

265-
SELECT append_partition('range_rel');
264+
SELECT append_partition('test.range_rel');
266265
NOTICE: Appending new partition...
267266
NOTICE: Done!
268267
append_partition
269268
------------------
270269

271270
(1 row)
272271

273-
SELECT prepend_partition('range_rel');
272+
SELECT prepend_partition('test.range_rel');
274273
NOTICE: Prepending new partition...
275274
NOTICE: Done!
276275
prepend_partition
@@ -281,21 +280,20 @@ NOTICE: Done!
281280
/*
282281
* Clean up
283282
*/
284-
SELECT drop_hash_partitions('hash_rel');
285-
NOTICE: trigger "hash_rel_update_trigger" for relation "hash_rel" does not exist, skipping
286-
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
283+
SELECT drop_hash_partitions('test.hash_rel');
287284
drop_hash_partitions
288285
----------------------
289286

290287
(1 row)
291288

292-
DROP TABLE hash_rel CASCADE;
289+
DROP TABLE test.hash_rel CASCADE;
293290
NOTICE: drop cascades to 3 other objects
294-
SELECT drop_range_partitions('num_range_rel');
291+
SELECT drop_range_partitions('test.num_range_rel');
295292
drop_range_partitions
296293
-----------------------
297294

298295
(1 row)
299296

300-
DROP TABLE num_range_rel CASCADE;
297+
DROP TABLE test.num_range_rel CASCADE;
298+
NOTICE: drop cascades to 6 other objects
301299
DROP EXTENSION pathman;

‎contrib/pathman/init.c

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,11 @@
11
#include "pathman.h"
22
#include "executor/spi.h"
33
#include "catalog/pg_type.h"
4-
54
#include "catalog/pg_class.h"
65
#include "catalog/pg_constraint.h"
76
#include "catalog/pg_operator.h"
8-
#include "utils/syscache.h"
97
#include "access/htup_details.h"
8+
#include "utils/syscache.h"
109
#include "utils/builtins.h"
1110
#include "utils/typcache.h"
1211
#include "utils/lsyscache.h"

‎contrib/pathman/sql/hash.sql

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ CREATE OR REPLACE FUNCTION create_hash_partitions(
88
) RETURNS VOID AS
99
$$
1010
BEGIN
11-
relation := lower(relation);
11+
relation := validate_relname(relation);
1212

1313
IF EXISTS (SELECT * FROM pathman_config WHERE relname = relation) THEN
1414
RAISE EXCEPTION 'Reltion "%s" has already been partitioned', relation;
@@ -41,7 +41,7 @@ BEGIN
4141
-- PERFORM create_hash_update_trigger(relation, attribute, partitions_count);
4242

4343
/* Notify backend about changes */
44-
PERFORM on_create_partitions(relid);
44+
PERFORM on_create_partitions(relation::regclass::integer);
4545
END
4646
$$ LANGUAGE plpgsql;
4747

@@ -67,8 +67,8 @@ DECLARE
6767
END $body$ LANGUAGE plpgsql;';
6868
trigger TEXT := '
6969
CREATE TRIGGER %s_insert_trigger
70-
BEFORE INSERT ON %1$s
71-
FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_insert_trigger_func();';
70+
BEFORE INSERT ON %s
71+
FOR EACH ROW EXECUTE PROCEDURE %2$s_hash_insert_trigger_func();';
7272
fields TEXT;
7373
fields_format TEXT;
7474
insert_stmt TEXT;
@@ -90,8 +90,7 @@ BEGIN
9090

9191
/* format and create new trigger for relation */
9292
func := format(func, relation, attr, partitions_count, insert_stmt);
93-
94-
trigger := format(trigger, relation::regclass::text);
93+
trigger := format(trigger, get_schema_qualified_name(relation::regclass), relation);
9594
EXECUTE func;
9695
EXECUTE trigger;
9796
END
@@ -125,7 +124,9 @@ CREATE OR REPLACE FUNCTION drop_hash_triggers(IN relation TEXT)
125124
RETURNS VOID AS
126125
$$
127126
BEGIN
128-
EXECUTE format('DROP TRIGGER IF EXISTS %s_insert_trigger ON %1$s', relation::regclass::text);
127+
EXECUTE format('DROP TRIGGER IF EXISTS %s_insert_trigger ON %s'
128+
, get_schema_qualified_name(relation::regclass)
129+
, relation);
129130
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func()', relation::regclass::text);
130131
-- EXECUTE format('DROP TRIGGER IF EXISTS %s_update_trigger ON %1$s', relation);
131132
-- EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func()', relation);

0 commit comments

Comments
 (0)