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

Commit6cf884f

Browse files
committed
pathman: check if partitioning key is nullable
1 parent381a14d commit6cf884f

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 partnumIN0..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+
CREATEOR REPLACE FUNCTION @extschema@.is_attribute_nullable(
147+
p_relationTEXT
148+
, p_attnameTEXT
149+
, OUT p_nullableBOOLEAN)
150+
RETURNSBOOLEANAS
151+
$$
152+
BEGIN
153+
SELECT NOT attnotnull INTO p_nullable
154+
FROM pg_typeJOIN pg_attributeon atttypid="oid"
155+
WHERE attrelid= p_relation::regclass::oidand 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_configWHERE 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 ISNULL 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 ISNULL 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 INTOtest.hash_relVALUES (2,2);
1212
INSERT INTOtest.hash_relVALUES (3,3);
1313
SELECTpathman.create_hash_partitions('test.hash_rel','value',3);
14+
ALTERTABLEtest.hash_rel ALTER COLUMN valueSETNOT NULL;
15+
SELECTpathman.create_hash_partitions('test.hash_rel','value',3);
1416
SELECTCOUNT(*)FROMtest.hash_rel;
1517
SELECTCOUNT(*)FROM ONLYtest.hash_rel;
1618
INSERT INTOtest.hash_relVALUES (4,4);
@@ -27,6 +29,8 @@ CREATE INDEX ON test.range_rel (dt);
2729
INSERT INTOtest.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
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,2);
32+
ALTERTABLEtest.range_rel ALTER COLUMN dtSETNOT NULL;
33+
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,2);
3034
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL);
3135
SELECTCOUNT(*)FROMtest.range_rel;
3236
SELECTCOUNT(*)FROM ONLYtest.range_rel;
@@ -142,7 +146,7 @@ DROP TABLE test.range_rel CASCADE;
142146
/* Test automatic partition creation*/
143147
CREATETABLEtest.range_rel (
144148
idSERIALPRIMARY KEY,
145-
dtTIMESTAMP);
149+
dtTIMESTAMPNOT NULL);
146150
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'10 days'::INTERVAL,1);
147151
INSERT INTOtest.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
CREATETABLEhash_rel (
181185
idSERIALPRIMARY KEY,
182-
valueINTEGER);
186+
valueINTEGERNOT NULL);
183187
INSERT INTO hash_rel (value)SELECT gFROM generate_series(1,10000)as g;
184188
SELECT create_hash_partitions('hash_rel','value',3);
185189
EXPLAIN (COSTS OFF)SELECT*FROM hash_relWHERE id=1234;
186190

187191
/* Range*/
188192
CREATETABLErange_rel (
189193
idSERIALPRIMARY KEY,
190-
dtTIMESTAMP);
194+
dtTIMESTAMPNOT NULL);
191195
INSERT INTO range_rel (dt)SELECT gFROM 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)
[8]ページ先頭
©2009-2025 Movatter.jp