[BUG]Missing REPLICA IDENTITY check when DROP NOT NULL

tanghy.fnst@fujitsu.com <tanghy.fnst@fujitsu.com>

From: "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>
To: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
Date: 2021-11-24T07:04:51Z
Lists: pgsql-hackers

Attachments

Hi, 

I think I found a problem related to replica identity. According to PG doc at [1], replica identity includes only columns marked NOT NULL. 
But in fact users can accidentally break this rule as follows:

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);

As a result, some operations on newly added null value will cause unexpected failure as below:

postgres=# delete from tbl;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

In the log, I can also see an assertion failure when deleting null value:
TRAP: FailedAssertion("!nulls[i]", File: "heapam.c", Line: 8439, PID: 274656)

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.

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.
Thanks Hou for helping me write/review this patch.

By the way, replica identity was introduced in PG9.4, so this problem exists in
all supported versions.

[1] https://www.postgresql.org/docs/current/sql-altertable.html

Regards,
Tang

Commits

  1. Block ALTER TABLE .. DROP NOT NULL on columns in replica identity index