Re: [BUG]Missing REPLICA IDENTITY check when DROP NOT NULL - Mailing list pgsql-hackers

On Wed, Nov 24, 2021 at 07:04:51AM +0000, tanghy.fnst@fujitsu.com wrote:
> create table tbl (a int not null unique);
> alter table tbl replica identity using INDEX tbl_a_key;
> alter table tbl alter column a drop not null;
> insert into tbl values (null);

Oops.  Yes, that's obviously not good.

> To solve the above problem, I think it's better to add a check when
> executing  ALTER COLUMN DROP NOT NULL,
> and report an error if this column is part of replica identity.

I'd say that you are right to block the operation.  I'll try to play a
bit with this stuff tomorrow.

> Attaching a patch that disallow DROP NOT NULL on a column if it's in
> a REPLICA IDENTITY index. Also added a test in it.

       if (indexStruct->indkey.values[i] == attnum)
           ereport(ERROR,
                   (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-                   errmsg("column \"%s\" is in a primary key",
+                   errmsg(ngettext("column \"%s\" is in a primary key",
+                                   "column \"%s\" is in a REPLICA IDENTITY index",
+                                   indexStruct->indisprimary),
                    colName)));
Using ngettext() looks incorrect to me here as it is used to get the
plural form of a string, so you'd better make these completely
separated instead.
--
Michael