Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit37827de

Browse files
committed
Block ALTER TABLE .. DROP NOT NULL on columns in replica identity index
Replica identities that depend directly on an index rely on a set ofproperties, one of them being that all the columns defined in this indexhave to be marked as NOT NULL. There was a hole in the logic with ALTERTABLE DROP NOT NULL, where it was possible to remove the NOT NULLproperty of a column part of an index used as replica identity, so blockit to avoid problems with logical decoding down the road.The same check was already done columns part of a primary key, so thefix is straight-forward.Author: Haiying Tang, Hou ZhijieReviewed-by: Dilip Kumar, Michael PaquierDiscussion:https://postgr.es/m/OS0PR01MB6113338C102BEE8B2FFC5BD9FB619@OS0PR01MB6113.jpnprd01.prod.outlook.comBackpatch-through: 10
1 parent4995522 commit37827de

File tree

3 files changed

+30
-9
lines changed

3 files changed

+30
-9
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 22 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -6677,7 +6677,8 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
66776677
colName, RelationGetRelationName(rel))));
66786678

66796679
/*
6680-
* Check that the attribute is not in a primary key
6680+
* Check that the attribute is not in a primary key or in an index used as
6681+
* a replica identity.
66816682
*
66826683
* Note: we'll throw error even if the pkey index is not valid.
66836684
*/
@@ -6697,20 +6698,32 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
66976698
elog(ERROR, "cache lookup failed for index %u", indexoid);
66986699
indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
66996700

6700-
/* If the index is not a primary key, skip the check */
6701-
if (indexStruct->indisprimary)
6701+
/*
6702+
* If the index is not a primary key or an index used as replica
6703+
* identity, skip the check.
6704+
*/
6705+
if (indexStruct->indisprimary || indexStruct->indisreplident)
67026706
{
67036707
/*
6704-
* Loop over each attribute in the primary key and see if it
6705-
* matches the to-be-altered attribute
6708+
* Loop over each attribute in the primary key or the index used
6709+
* as replica identity and see if it matches the to-be-altered
6710+
* attribute.
67066711
*/
67076712
for (i = 0; i < indexStruct->indnkeyatts; i++)
67086713
{
67096714
if (indexStruct->indkey.values[i] == attnum)
6710-
ereport(ERROR,
6711-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6712-
errmsg("column \"%s\" is in a primary key",
6713-
colName)));
6715+
{
6716+
if (indexStruct->indisprimary)
6717+
ereport(ERROR,
6718+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6719+
errmsg("column \"%s\" is in a primary key",
6720+
colName)));
6721+
else
6722+
ereport(ERROR,
6723+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6724+
errmsg("column \"%s\" is in index used as replica identity",
6725+
colName)));
6726+
}
67146727
}
67156728
}
67166729

‎src/test/regress/expected/replica_identity.out

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -223,6 +223,10 @@ ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint;
223223
Indexes:
224224
"test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY
225225

226+
-- ALTER TABLE DROP NOT NULL is not allowed for columns part of an index
227+
-- used as replica identity.
228+
ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL;
229+
ERROR: column "id" is in index used as replica identity
226230
DROP TABLE test_replica_identity;
227231
DROP TABLE test_replica_identity2;
228232
DROP TABLE test_replica_identity3;

‎src/test/regress/sql/replica_identity.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,6 +94,10 @@ ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_ide
9494
ALTERTABLE test_replica_identity3 ALTER COLUMN id TYPEbigint;
9595
\d test_replica_identity3
9696

97+
-- ALTER TABLE DROP NOT NULL is not allowed for columns part of an index
98+
-- used as replica identity.
99+
ALTERTABLE test_replica_identity3 ALTER COLUMN id DROPNOT NULL;
100+
97101
DROPTABLE test_replica_identity;
98102
DROPTABLE test_replica_identity2;
99103
DROPTABLE test_replica_identity3;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp