Hello
Here is two patches with NOTICE ereport: one for partitions operations and one
for "set not null" (for consistency)
regards, Sergei
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2a2c161695..4ec61d4833 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6329,7 +6329,7 @@ NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr)
if (ConstraintImpliedByRelConstraint(rel, list_make1(nnulltest), NIL))
{
- ereport(DEBUG1,
+ ereport(NOTICE,
(errmsg("existing constraints on column \"%s\".\"%s\" are sufficient to prove that it does not contain nulls",
RelationGetRelationName(rel), NameStr(attr->attname))));
return true;
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 95d5bee029..fb8b3583f1 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1089,12 +1089,14 @@ alter table atacc1 drop constraint atacc1_constr_invalid;
update atacc1 set test_a = 1;
alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null);
alter table atacc1 alter test_a set not null;
+NOTICE: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls
delete from atacc1;
insert into atacc1 values (2, null);
alter table atacc1 alter test_a drop not null;
-- test multiple set not null at same time
-- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan
alter table atacc1 alter test_a set not null, alter test_b set not null;
+NOTICE: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls
ERROR: column "test_b" contains null values
-- commands order has no importance
alter table atacc1 alter test_b set not null, alter test_a set not null;
@@ -1106,6 +1108,8 @@ alter table atacc1 alter test_a drop not null, alter test_b drop not null;
-- both column has check constraints
alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);
alter table atacc1 alter test_b set not null, alter test_a set not null;
+NOTICE: existing constraints on column "atacc1"."test_b" are sufficient to prove that it does not contain nulls
+NOTICE: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls
drop table atacc1;
-- test inheritance
create table parent (a int);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2e792edbcf..2a2c161695 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15336,11 +15336,11 @@ QueuePartitionConstraintValidation(List **wqueue, Relation scanrel,
if (PartConstraintImpliedByRelConstraint(scanrel, partConstraint))
{
if (!validate_default)
- ereport(INFO,
+ ereport(NOTICE,
(errmsg("partition constraint for table \"%s\" is implied by existing constraints",
RelationGetRelationName(scanrel))));
else
- ereport(INFO,
+ ereport(NOTICE,
(errmsg("updated partition constraint for default partition \"%s\" is implied by existing constraints",
RelationGetRelationName(scanrel))));
return;
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 7d8907b2b4..34a325bce8 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -1253,7 +1253,7 @@ check_default_partition_contents(Relation parent, Relation default_rel,
*/
if (PartConstraintImpliedByRelConstraint(default_rel, def_part_constraints))
{
- ereport(INFO,
+ ereport(NOTICE,
(errmsg("updated partition constraint for default partition \"%s\" is implied by existing constraints",
RelationGetRelationName(default_rel))));
return;
@@ -1304,7 +1304,7 @@ check_default_partition_contents(Relation parent, Relation default_rel,
if (PartConstraintImpliedByRelConstraint(part_rel,
def_part_constraints))
{
- ereport(INFO,
+ ereport(NOTICE,
(errmsg("updated partition constraint for default partition \"%s\" is implied by existing constraints",
RelationGetRelationName(part_rel))));
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 3578b8009b..95d5bee029 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3633,11 +3633,11 @@ ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
ALTER TABLE part_3_4 ALTER a SET NOT NULL;
ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
-INFO: partition constraint for table "part_3_4" is implied by existing constraints
+NOTICE: partition constraint for table "part_3_4" is implied by existing constraints
-- check if default partition scan skipped
ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6));
CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66);
-INFO: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
+NOTICE: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
-- check validation when attaching range partitions
CREATE TABLE range_parted (
a int,
@@ -3662,7 +3662,7 @@ CREATE TABLE part2 (
b int NOT NULL CHECK (b >= 10 AND b < 18)
);
ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20);
-INFO: partition constraint for table "part2" is implied by existing constraints
+NOTICE: partition constraint for table "part2" is implied by existing constraints
-- Create default partition
CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT;
-- Only one default partition is allowed, hence, following should give error
@@ -3690,13 +3690,13 @@ ERROR: partition constraint is violated by some row
DELETE FROM part_5_a WHERE a NOT IN (3);
ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5);
ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
-INFO: partition constraint for table "part_5" is implied by existing constraints
+NOTICE: partition constraint for table "part_5" is implied by existing constraints
ALTER TABLE list_parted2 DETACH PARTITION part_5;
ALTER TABLE part_5 DROP CONSTRAINT check_a;
-- scan should again be skipped, even though NOT NULL is now a column property
ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL;
ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
-INFO: partition constraint for table "part_5" is implied by existing constraints
+NOTICE: partition constraint for table "part_5" is implied by existing constraints
-- Check the case where attnos of the partitioning columns in the table being
-- attached differs from the parent. It should not affect the constraint-
-- checking logic that allows to skip the scan.
@@ -3707,7 +3707,7 @@ CREATE TABLE part_6 (
);
ALTER TABLE part_6 DROP c;
ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6);
-INFO: partition constraint for table "part_6" is implied by existing constraints
+NOTICE: partition constraint for table "part_6" is implied by existing constraints
-- Similar to above, but the table being attached is a partitioned table
-- whose partition has still different attnos for the root partitioning
-- columns.
@@ -3725,10 +3725,10 @@ CREATE TABLE part_7_a_null (
);
ALTER TABLE part_7_a_null DROP c, DROP d, DROP e;
ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null);
-INFO: partition constraint for table "part_7_a_null" is implied by existing constraints
+NOTICE: partition constraint for table "part_7_a_null" is implied by existing constraints
ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
-INFO: partition constraint for table "part_7" is implied by existing constraints
-INFO: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
+NOTICE: partition constraint for table "part_7" is implied by existing constraints
+NOTICE: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
-- Same example, but check this time that the constraint correctly detects
-- violating rows
ALTER TABLE list_parted2 DETACH PARTITION part_7;
@@ -3742,7 +3742,7 @@ SELECT tableoid::regclass, a, b FROM part_7 order by a;
(2 rows)
ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
-INFO: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
+NOTICE: updated partition constraint for default partition "list_parted2_def" is implied by existing constraints
ERROR: partition constraint is violated by some row
-- check that leaf partitions of default partition are scanned when
-- attaching a partitioned table.
@@ -3779,12 +3779,12 @@ CREATE TABLE quuux1 (a int, b text);
ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate!
CREATE TABLE quuux2 (a int, b text);
ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation
-INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints
+NOTICE: updated partition constraint for default partition "quuux_default1" is implied by existing constraints
DROP TABLE quuux1, quuux2;
-- should validate for quuux1, but not for quuux2
CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1);
CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);
-INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints
+NOTICE: updated partition constraint for default partition "quuux_default1" is implied by existing constraints
DROP TABLE quuux;
-- check validation when attaching hash partitions
-- Use hand-rolled hash functions and operator class to get predictable result
@@ -4019,7 +4019,7 @@ delete from defpart_attach_test_d where a = 1;
alter table defpart_attach_test_d add check (a > 1);
-- should be attached successfully and without needing to be scanned
alter table defpart_attach_test attach partition defpart_attach_test_d default;
-INFO: partition constraint for table "defpart_attach_test_d" is implied by existing constraints
+NOTICE: partition constraint for table "defpart_attach_test_d" is implied by existing constraints
-- check that attaching a partition correctly reports any rows in the default
-- partition that should not be there for the new partition to be attached
-- successfully
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 262abf2bfb..40f972ba12 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1127,7 +1127,7 @@ alter table defcheck_def drop c;
alter table defcheck attach partition defcheck_def default;
alter table defcheck_def add check (b <= 0 and b is not null);
create table defcheck_1 partition of defcheck for values in (1, null);
-INFO: updated partition constraint for default partition "defcheck_def" is implied by existing constraints
+NOTICE: updated partition constraint for default partition "defcheck_def" is implied by existing constraints
-- test that complex default partition constraints are enforced correctly
insert into defcheck_def values (0, 0);
create table defcheck_0 partition of defcheck for values in (0);
