On 2017/02/09 15:22, amul sul wrote: > About 0001-Check-partition-strategy-in-ATExecDropNotNull.patch, > following test is already covered in alter_table.sql @ Line # 1918, > instead of this kindly add test for list_partition: > > 77 +-- cannot drop NOT NULL constraint of a range partition key column > 78 +ALTER TABLE range_parted ALTER a DROP NOT NULL; > 79 +
Thanks for the review! You're right. Updated patch attached. Thanks, Amit
>From a4335048e92462fb55fa6db0d830c7c066cd7011 Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Fri, 10 Feb 2017 14:52:18 +0900 Subject: [PATCH] Check partition strategy in ATExecDropNotNull We should prevent dropping the NOT NULL constraint on a column only if the column is in the *range* partition key (as the error message also says). Add a regression test while at it. Reported by: Amul Sul Patch by: Amit Langote Report: https://postgr.es/m/CAAJ_b95g5AgkpJKbLajAt8ovKub874-B9X08PiOqHvVfMO2mLw%40mail.gmail.com --- src/backend/commands/tablecmds.c | 22 +++++++++++++--------- src/test/regress/expected/alter_table.out | 4 ++++ src/test/regress/sql/alter_table.sql | 5 +++++ 3 files changed, 22 insertions(+), 9 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 37a4c4a3d6..f33aa70da6 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -5593,18 +5593,22 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { PartitionKey key = RelationGetPartitionKey(rel); - int partnatts = get_partition_natts(key), - i; - for (i = 0; i < partnatts; i++) + if (get_partition_strategy(key) == PARTITION_STRATEGY_RANGE) { - AttrNumber partattnum = get_partition_col_attnum(key, i); + int partnatts = get_partition_natts(key), + i; - if (partattnum == attnum) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("column \"%s\" is in range partition key", - colName))); + for (i = 0; i < partnatts; i++) + { + AttrNumber partattnum = get_partition_col_attnum(key, i); + + if (partattnum == attnum) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("column \"%s\" is in range partition key", + colName))); + } } } diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index d8e7b61294..b0e80a7788 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3029,6 +3029,10 @@ ERROR: cannot alter type of column referenced in partition key expression -- cannot drop NOT NULL on columns in the range partition key ALTER TABLE partitioned ALTER COLUMN a DROP NOT NULL; ERROR: column "a" is in range partition key +-- it's fine however to drop one on the list partition key column +CREATE TABLE list_partitioned (a int not null) partition by list (a); +ALTER TABLE list_partitioned ALTER a DROP NOT NULL; +DROP TABLE list_partitioned; -- partitioned table cannot participate in regular inheritance CREATE TABLE foo ( a int, diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 1f551ec53c..7513769359 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1917,6 +1917,11 @@ ALTER TABLE partitioned ALTER COLUMN b TYPE char(5); -- cannot drop NOT NULL on columns in the range partition key ALTER TABLE partitioned ALTER COLUMN a DROP NOT NULL; +-- it's fine however to drop one on the list partition key column +CREATE TABLE list_partitioned (a int not null) partition by list (a); +ALTER TABLE list_partitioned ALTER a DROP NOT NULL; +DROP TABLE list_partitioned; + -- partitioned table cannot participate in regular inheritance CREATE TABLE foo ( a int, -- 2.11.0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
