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
0001-Disallow-DROP-NOT-NULL-for-column-in-the-REPLICA-IDE.patch
Description: 0001-Disallow-DROP-NOT-NULL-for-column-in-the-REPLICA-IDE.patch
