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

Commit216156f

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 parent6365e3a commit216156f

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
@@ -6364,7 +6364,8 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
63646364
colName, RelationGetRelationName(rel))));
63656365

63666366
/*
6367-
* Check that the attribute is not in a primary key
6367+
* Check that the attribute is not in a primary key or in an index used as
6368+
* a replica identity.
63686369
*
63696370
* Note: we'll throw error even if the pkey index is not valid.
63706371
*/
@@ -6384,20 +6385,32 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
63846385
elog(ERROR, "cache lookup failed for index %u", indexoid);
63856386
indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
63866387

6387-
/* If the index is not a primary key, skip the check */
6388-
if (indexStruct->indisprimary)
6388+
/*
6389+
* If the index is not a primary key or an index used as replica
6390+
* identity, skip the check.
6391+
*/
6392+
if (indexStruct->indisprimary || indexStruct->indisreplident)
63896393
{
63906394
/*
6391-
* Loop over each attribute in the primary key and see if it
6392-
* matches the to-be-altered attribute
6395+
* Loop over each attribute in the primary key or the index used
6396+
* as replica identity and see if it matches the to-be-altered
6397+
* attribute.
63936398
*/
63946399
for (i = 0; i < indexStruct->indnkeyatts; i++)
63956400
{
63966401
if (indexStruct->indkey.values[i] == attnum)
6397-
ereport(ERROR,
6398-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6399-
errmsg("column \"%s\" is in a primary key",
6400-
colName)));
6402+
{
6403+
if (indexStruct->indisprimary)
6404+
ereport(ERROR,
6405+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6406+
errmsg("column \"%s\" is in a primary key",
6407+
colName)));
6408+
else
6409+
ereport(ERROR,
6410+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6411+
errmsg("column \"%s\" is in index used as replica identity",
6412+
colName)));
6413+
}
64016414
}
64026415
}
64036416

‎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