Skip to content

Commit 988030b

Browse files
zilderzilder
committed
pathman: check for foreing key constraints before partitioning
1 parent 6cf884f commit 988030b

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_type TEXT;
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_config WHERE 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 partnum IN 0..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+
CREATE OR REPLACE FUNCTION @extschema@.common_relation_checks(
165+
p_relation TEXT
166+
, p_attribute TEXT)
167+
RETURNS BOOLEAN AS
168+
$$
169+
DECLARE
170+
v_rec RECORD;
171+
is_referenced BOOLEAN;
172+
BEGIN
173+
IF EXISTS (SELECT * FROM @extschema@.pathman_config WHERE 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_rec IN (SELECT *
183+
FROM pg_constraint WHERE 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
i INTEGER;
1717
BEGIN
1818
p_relation := @extschema@.validate_relname(p_relation);
19-
20-
IF EXISTS (SELECT * FROM @extschema@.pathman_config WHERE 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 IS NULL THEN
@@ -107,19 +100,12 @@ DECLARE
107100
i INTEGER;
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_config WHERE 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 IS NULL THEN
125111
EXECUTE format('SELECT count(*), max(%s) FROM %s'
@@ -199,19 +185,12 @@ DECLARE
199185
i INTEGER := 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_config WHERE 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
i INTEGER := 0;
270249
BEGIN
271250
p_relation := @extschema@.validate_relname(p_relation);
272-
273-
IF EXISTS (SELECT * FROM @extschema@.pathman_config WHERE 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_rel WHERE dt = '2015-12-15';
210210

211-
/* Test exception handling on partitioning */
212211
CREATE TABLE messages(id SERIAL PRIMARY KEY, msg TEXT);
213212
CREATE TABLE replies(id SERIAL PRIMARY KEY, message_id INTEGER REFERENCES messages(id), msg TEXT);
214213
INSERT INTO messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;
215214
INSERT INTO replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;
216215
SELECT create_range_partitions('messages', 'id', 1, 100, 2);
216+
ALTER TABLE replies DROP CONSTRAINT 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)