[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
- 0001-Disallow-DROP-NOT-NULL-for-column-in-the-REPLICA-IDE.patch (application/octet-stream) patch 0001
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
-
Block ALTER TABLE .. DROP NOT NULL on columns in replica identity index
- 817c469c2a16 10.20 landed
- dffe80e5500d 11.15 landed
- 216156fec3d8 12.10 landed
- 37827de430de 13.6 landed
- e415916e24cc 14.2 landed
- f0d43947a1b0 15.0 landed