From: | "tanghy(dot)fnst(at)fujitsu(dot)com" <tanghy(dot)fnst(at)fujitsu(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Cc: | "houzj(dot)fnst(at)fujitsu(dot)com" <houzj(dot)fnst(at)fujitsu(dot)com> |
Subject: | [BUG]Missing REPLICA IDENTITY check when DROP NOT NULL |
Date: | 2021-11-24 07:04:51 |
Message-ID: | OS0PR01MB6113338C102BEE8B2FFC5BD9FB619@OS0PR01MB6113.jpnprd01.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
0001-Disallow-DROP-NOT-NULL-for-column-in-the-REPLICA-IDE.patch | application/octet-stream | 3.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2021-11-24 07:17:14 | Re: Sequence's value can be rollback after a crashed recovery. |
Previous Message | Masahiko Sawada | 2021-11-24 06:45:45 | Re: parallel vacuum comments |