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

Commit988030b

Browse files
committed
pathman: check for foreing key constraints before partitioning
1 parent6cf884f commit988030b

File tree

5 files changed

+61
-48
lines changed

5 files changed

+61
-48
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out‎

Lines changed: 15 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -862,19 +862,28 @@ EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt = '2015-12-15';
862862
Filter: (dt = 'Tue Dec 15 00:00:00 2015'::timestamp without time zone)
863863
(3 rows)
864864

865-
/* Test exception handling on partitioning */
866865
CREATE TABLE messages(id SERIAL PRIMARY KEY, msg TEXT);
867866
CREATE TABLE replies(id SERIAL PRIMARY KEY, message_id INTEGER REFERENCES messages(id), msg TEXT);
868867
INSERT INTO messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;
869868
INSERT INTO replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;
870869
SELECT create_range_partitions('messages', 'id', 1, 100, 2);
870+
WARNING: Foreign key 'replies_message_id_fkey' references to the relation 'public.messages'
871+
ERROR: Relation 'public.messages' is referenced from other relations P0001
872+
ALTER TABLE replies DROP CONSTRAINT replies_message_id_fkey;
873+
SELECT create_range_partitions('messages', 'id', 1, 100, 2);
871874
NOTICE: sequence "messages_seq" does not exist, skipping
872875
NOTICE: Copying data to partitions...
873-
ERROR: update or delete on table "messages" violates foreign key constraint "replies_message_id_fkey" on table "replies" 23503
874-
EXPLAIN (COSTS OFF) SELECT * FROM messages;
875-
QUERY PLAN
876-
----------------------
877-
Seq Scan on messages
876+
create_range_partitions
877+
-------------------------
878+
2
878879
(1 row)
879880

881+
EXPLAIN (COSTS OFF) SELECT * FROM messages;
882+
QUERY PLAN
883+
------------------------------
884+
Append
885+
-> Seq Scan on messages_1
886+
-> Seq Scan on messages_2
887+
(3 rows)
888+
880889
DROP EXTENSION pg_pathman;

‎contrib/pg_pathman/hash.sql‎

Lines changed: 2 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -11,20 +11,13 @@ DECLARE
1111
v_typeTEXT;
1212
BEGIN
1313
relation := @extschema@.validate_relname(relation);
14-
v_type := @extschema@.get_attribute_type_name(relation, attribute);
15-
16-
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= relation) THEN
17-
RAISE EXCEPTION'Relation "%" has already been partitioned', relation;
18-
END IF;
14+
PERFORM @extschema@.common_relation_checks(relation, attribute);
1915

16+
v_type := @extschema@.get_attribute_type_name(relation, attribute);
2017
IF v_type::regtype!='integer'::regtype THEN
2118
RAISE EXCEPTION'Attribute type must be INTEGER';
2219
END IF;
2320

24-
IF @extschema@.is_attribute_nullable(relation, attribute) THEN
25-
RAISE EXCEPTION'Partitioning key''%'' must be NOT NULL', attribute;
26-
END IF;
27-
2821
/* Create partitions and update pg_pathman configuration*/
2922
FOR partnumIN0..partitions_count-1
3023
LOOP

‎contrib/pg_pathman/init.sql‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,44 @@ $$
158158
LANGUAGE plpgsql;
159159

160160

161+
/*
162+
* Aggregates several common relation checks before partitioning. Suitable for every partitioning type.
163+
*/
164+
CREATEOR REPLACE FUNCTION @extschema@.common_relation_checks(
165+
p_relationTEXT
166+
, p_attributeTEXT)
167+
RETURNSBOOLEANAS
168+
$$
169+
DECLARE
170+
v_rec RECORD;
171+
is_referencedBOOLEAN;
172+
BEGIN
173+
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
174+
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
175+
END IF;
176+
177+
IF @extschema@.is_attribute_nullable(p_relation, p_attribute) THEN
178+
RAISE EXCEPTION'Partitioning key''%'' must be NOT NULL', p_attribute;
179+
END IF;
180+
181+
/* Check if there are foreign keys reference to the relation*/
182+
FOR v_recIN (SELECT*
183+
FROM pg_constraintWHERE confrelid= p_relation::regclass::oid)
184+
LOOP
185+
is_referenced := TRUE;
186+
RAISE WARNING'Foreign key''%'' references to the relation''%''',v_rec.conname, p_relation;
187+
END LOOP;
188+
189+
IF is_referenced THEN
190+
RAISE EXCEPTION'Relation''%'' is referenced from other relations', p_relation;
191+
END IF;
192+
193+
RETURN TRUE;
194+
END
195+
$$
196+
LANGUAGE plpgsql;
197+
198+
161199
/*
162200
* Validates relation name. It must be schema qualified
163201
*/

‎contrib/pg_pathman/range.sql‎

Lines changed: 4 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -16,14 +16,7 @@ DECLARE
1616
iINTEGER;
1717
BEGIN
1818
p_relation := @extschema@.validate_relname(p_relation);
19-
20-
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
21-
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
22-
END IF;
23-
24-
IF @extschema@.is_attribute_nullable(p_relation, p_attribute) THEN
25-
RAISE EXCEPTION'Partitioning key''%'' must be NOT NULL', p_attribute;
26-
END IF;
19+
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
2720

2821
/* Try to determine partitions count if not set*/
2922
IF p_count ISNULL THEN
@@ -107,19 +100,12 @@ DECLARE
107100
iINTEGER;
108101
BEGIN
109102
p_relation := @extschema@.validate_relname(p_relation);
103+
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
110104

111105
IF p_count<=0 THEN
112106
RAISE EXCEPTION'Partitions count must be greater than zero';
113107
END IF;
114108

115-
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
116-
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
117-
END IF;
118-
119-
IF @extschema@.is_attribute_nullable(p_relation, p_attribute) THEN
120-
RAISE EXCEPTION'Partitioning key''%'' must be NOT NULL', p_attribute;
121-
END IF;
122-
123109
/* Try to determine partitions count if not set*/
124110
IF p_count ISNULL THEN
125111
EXECUTE format('SELECT count(*), max(%s) FROM %s'
@@ -199,19 +185,12 @@ DECLARE
199185
iINTEGER :=0;
200186
BEGIN
201187
p_relation := @extschema@.validate_relname(p_relation);
188+
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
202189

203190
IF p_interval<=0 THEN
204191
RAISE EXCEPTION'Interval must be positive';
205192
END IF;
206193

207-
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
208-
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
209-
END IF;
210-
211-
IF @extschema@.is_attribute_nullable(p_relation, p_attribute) THEN
212-
RAISE EXCEPTION'Partitioning key''%'' must be NOT NULL', p_attribute;
213-
END IF;
214-
215194
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
216195
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
217196

@@ -269,14 +248,7 @@ DECLARE
269248
iINTEGER :=0;
270249
BEGIN
271250
p_relation := @extschema@.validate_relname(p_relation);
272-
273-
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
274-
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
275-
END IF;
276-
277-
IF @extschema@.is_attribute_nullable(p_relation, p_attribute) THEN
278-
RAISE EXCEPTION'Partitioning key''%'' must be NOT NULL', p_attribute;
279-
END IF;
251+
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
280252

281253
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
282254
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);

‎contrib/pg_pathman/sql/pg_pathman.sql‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -208,12 +208,13 @@ SELECT drop_range_partitions('range_rel', TRUE);
208208
SELECT create_partitions_from_range('range_rel','dt','2015-01-01'::date,'2015-12-01'::date,'1 month'::interval);
209209
EXPLAIN (COSTS OFF)SELECT*FROM range_relWHERE dt='2015-12-15';
210210

211-
/* Test exception handling on partitioning*/
212211
CREATETABLEmessages(idSERIALPRIMARY KEY, msgTEXT);
213212
CREATETABLEreplies(idSERIALPRIMARY KEY, message_idINTEGERREFERENCES messages(id), msgTEXT);
214213
INSERT INTO messagesSELECT g, md5(g::text)FROM generate_series(1,10)as g;
215214
INSERT INTO repliesSELECT g, g, md5(g::text)FROM generate_series(1,10)as g;
216215
SELECT create_range_partitions('messages','id',1,100,2);
216+
ALTERTABLE replies DROPCONSTRAINT replies_message_id_fkey;
217+
SELECT create_range_partitions('messages','id',1,100,2);
217218
EXPLAIN (COSTS OFF)SELECT*FROM messages;
218219

219220
DROP EXTENSION pg_pathman;

0 commit comments

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