Skip to content

Commit 6cf884f

Browse files
zilderzilder
committed
pathman: check if partitioning key is nullable
1 parent 381a14d commit 6cf884f

File tree

5 files changed

+58
-11
lines changed

5 files changed

+58
-11
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,9 @@ INSERT INTO test.hash_rel VALUES (1, 1);
99
INSERT INTO test.hash_rel VALUES (2, 2);
1010
INSERT INTO test.hash_rel VALUES (3, 3);
1111
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
12+
ERROR: Partitioning key 'value' must be NOT NULL
13+
ALTER TABLE test.hash_rel ALTER COLUMN value SET NOT NULL;
14+
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
1215
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
1316
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
1417
NOTICE: Copying data to partitions...
@@ -52,6 +55,9 @@ CREATE INDEX ON test.range_rel (dt);
5255
INSERT INTO test.range_rel (dt, txt)
5356
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
5457
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
58+
ERROR: Partitioning key 'dt' must be NOT NULL P0001
59+
ALTER TABLE test.range_rel ALTER COLUMN dt SET NOT NULL;
60+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
5561
ERROR: Not enough partitions to fit all the values of 'dt' P0001
5662
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL);
5763
NOTICE: sequence "range_rel_seq" does not exist, skipping
@@ -613,7 +619,7 @@ NOTICE: drop cascades to 7 other objects
613619
/* Test automatic partition creation */
614620
CREATE TABLE test.range_rel (
615621
id SERIAL PRIMARY KEY,
616-
dt TIMESTAMP);
622+
dt TIMESTAMP NOT NULL);
617623
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
618624
NOTICE: Copying data to partitions...
619625
create_range_partitions
@@ -719,7 +725,7 @@ CREATE EXTENSION pg_pathman;
719725
/* Hash */
720726
CREATE TABLE hash_rel (
721727
id SERIAL PRIMARY KEY,
722-
value INTEGER);
728+
value INTEGER NOT NULL);
723729
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
724730
SELECT create_hash_partitions('hash_rel', 'value', 3);
725731
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
@@ -745,7 +751,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
745751
/* Range */
746752
CREATE TABLE range_rel (
747753
id SERIAL PRIMARY KEY,
748-
dt TIMESTAMP);
754+
dt TIMESTAMP NOT NULL);
749755
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
750756
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
751757
NOTICE: sequence "range_rel_seq" does not exist, skipping

‎contrib/pg_pathman/hash.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,10 @@ BEGIN
2121
RAISE EXCEPTION 'Attribute type must be INTEGER';
2222
END IF;
2323

24+
IF @extschema@.is_attribute_nullable(relation, attribute) THEN
25+
RAISE EXCEPTION 'Partitioning key ''%'' must be NOT NULL', attribute;
26+
END IF;
27+
2428
/* Create partitions and update pg_pathman configuration */
2529
FOR partnum IN 0..partitions_count-1
2630
LOOP

‎contrib/pg_pathman/init.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,24 @@ $$
140140
LANGUAGE plpgsql;
141141

142142

143+
/*
144+
* Checks if attribute is nullable
145+
*/
146+
CREATE OR REPLACE FUNCTION @extschema@.is_attribute_nullable(
147+
p_relation TEXT
148+
, p_attname TEXT
149+
, OUT p_nullable BOOLEAN)
150+
RETURNS BOOLEAN AS
151+
$$
152+
BEGIN
153+
SELECT NOT attnotnull INTO p_nullable
154+
FROM pg_type JOIN pg_attribute on atttypid = "oid"
155+
WHERE attrelid = p_relation::regclass::oid and attname = lower(p_attname);
156+
END
157+
$$
158+
LANGUAGE plpgsql;
159+
160+
143161
/*
144162
* Validates relation name. It must be schema qualified
145163
*/

‎contrib/pg_pathman/range.sql

Lines changed: 20 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -17,14 +17,14 @@ DECLARE
1717
BEGIN
1818
p_relation := @extschema@.validate_relname(p_relation);
1919

20-
-- IF p_count <= 0 THEN
21-
-- RAISE EXCEPTION 'Partitions count must be greater than zero';
22-
-- END IF;
23-
2420
IF EXISTS (SELECT * FROM @extschema@.pathman_config WHERE relname = p_relation) THEN
2521
RAISE EXCEPTION 'Relation "%" has already been partitioned', p_relation;
2622
END IF;
2723

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;
27+
2828
/* Try to determine partitions count if not set */
2929
IF p_count IS NULL THEN
3030
EXECUTE format('SELECT count(*), max(%s) FROM %s'
@@ -116,6 +116,10 @@ BEGIN
116116
RAISE EXCEPTION 'Relation "%" has already been partitioned', p_relation;
117117
END IF;
118118

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+
119123
/* Try to determine partitions count if not set */
120124
IF p_count IS NULL THEN
121125
EXECUTE format('SELECT count(*), max(%s) FROM %s'
@@ -204,6 +208,10 @@ BEGIN
204208
RAISE EXCEPTION 'Relation "%" has already been partitioned', p_relation;
205209
END IF;
206210

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+
207215
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
208216
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
209217

@@ -266,6 +274,10 @@ BEGIN
266274
RAISE EXCEPTION 'Relation "%" has already been partitioned', p_relation;
267275
END IF;
268276

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;
280+
269281
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
270282
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
271283

@@ -975,7 +987,10 @@ DECLARE
975987
v_part_relid OID;
976988
BEGIN
977989
IF TG_OP = ''INSERT'' THEN
978-
v_part_relid := @extschema@.find_or_create_range_partition(TG_RELID, NEW.%s);
990+
IF NEW.%2$s IS NULL THEN
991+
RAISE EXCEPTION ''ERROR: NULL value in partitioning key'';
992+
END IF;
993+
v_part_relid := @extschema@.find_or_create_range_partition(TG_RELID, NEW.%2$s);
979994
IF NOT v_part_relid IS NULL THEN
980995
EXECUTE format(''INSERT INTO %%s SELECT $1.*'', v_part_relid::regclass)
981996
USING NEW;

‎contrib/pg_pathman/sql/pg_pathman.sql

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,8 @@ INSERT INTO test.hash_rel VALUES (1, 1);
1111
INSERT INTO test.hash_rel VALUES (2, 2);
1212
INSERT INTO test.hash_rel VALUES (3, 3);
1313
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
14+
ALTER TABLE test.hash_rel ALTER COLUMN value SET NOT NULL;
15+
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
1416
SELECT COUNT(*) FROM test.hash_rel;
1517
SELECT COUNT(*) FROM ONLY test.hash_rel;
1618
INSERT INTO test.hash_rel VALUES (4, 4);
@@ -27,6 +29,8 @@ CREATE INDEX ON test.range_rel (dt);
2729
INSERT INTO test.range_rel (dt, txt)
2830
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
2931
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
32+
ALTER TABLE test.range_rel ALTER COLUMN dt SET NOT NULL;
33+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
3034
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL);
3135
SELECT COUNT(*) FROM test.range_rel;
3236
SELECT COUNT(*) FROM ONLY test.range_rel;
@@ -142,7 +146,7 @@ DROP TABLE test.range_rel CASCADE;
142146
/* Test automatic partition creation */
143147
CREATE TABLE test.range_rel (
144148
id SERIAL PRIMARY KEY,
145-
dt TIMESTAMP);
149+
dt TIMESTAMP NOT NULL);
146150
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
147151
INSERT INTO test.range_rel (dt)
148152
SELECT generate_series('2015-01-01', '2015-04-30', '1 day'::interval);
@@ -179,15 +183,15 @@ CREATE EXTENSION pg_pathman;
179183
/* Hash */
180184
CREATE TABLE hash_rel (
181185
id SERIAL PRIMARY KEY,
182-
value INTEGER);
186+
value INTEGER NOT NULL);
183187
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
184188
SELECT create_hash_partitions('hash_rel', 'value', 3);
185189
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
186190

187191
/* Range */
188192
CREATE TABLE range_rel (
189193
id SERIAL PRIMARY KEY,
190-
dt TIMESTAMP);
194+
dt TIMESTAMP NOT NULL);
191195
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
192196
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
193197
SELECT merge_range_partitions('range_rel_1', 'range_rel_2');

0 commit comments

Comments
 (0)