Re: Re: Re: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem. - Mailing list pgsql-bugs

 

i looked into this, the reason for these failures was when the given name
for a constraint for a parent table propagates to the child table because
of inheritance the name conflicts and throws "mismatching constraint name"
error we added, let me show an example,

postgres=# create table test1(col1 int);
CREATE TABLE
postgres=# create table test2(col1 int not null);
CREATE TABLE
postgres=# create table child12() inherits ( test1,test2);
NOTICE:  merging multiple inherited definitions of column "col1"
CREATE TABLE
postgres=# \d+ child12
                                         Table "public.child12"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 col1   | integer |           | not null |         | plain   |             |              |
Not-null constraints:
    "test2_col1_not_null" NOT NULL "col1" (inherited)
Inherits: test1,
          test2
Access method: heap

postgres=# alter table test1 add constraint nn not null col1 not valid;
ERROR:  mismatching constraint name "nn"
DETAIL:  A not-null constraint named "test2_col1_not_null" already exists for this column.

I think we can fix this by throwing an error only if this constraint was added
directly to the table and not through inheritance/propagation from the parent,
we can do this using the "is_local" flag, i have checked and all tests passed.

  /*
* Throw an error if the proposed constraint name doesn't match the
* existing one.
*/
+ if (is_local && name &&
strcmp(name, NameStr(conform->conname)) != 0)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("mismatching constraint name \"%s\"", name),
errdetail("A not-null constraint named \"%s\" already exists for this column.",
 NameStr(conform->conname)));

also checking how other constraints handle this case like CHECK
and found it just appends to existing constraint

postgres=# \d+ child34
                                         Table "public.child34"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 a      | integer |           |          |         | plain   |             |              |
Check constraints:
    "c" CHECK (a > 1)
    "d" CHECK (a > 1)
Inherits: test3,
          test4
Access method: heap

but I don't think it makes sense for NOT NULL, thoughts?