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

Commitf0d4394

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 parent10260c7 commitf0d4394

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

71467146
/*
7147-
* Check that the attribute is not in a primary key
7147+
* Check that the attribute is not in a primary key or in an index used as
7148+
* a replica identity.
71487149
*
71497150
* Note: we'll throw error even if the pkey index is not valid.
71507151
*/
@@ -7164,20 +7165,32 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
71647165
elog(ERROR, "cache lookup failed for index %u", indexoid);
71657166
indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
71667167

7167-
/* If the index is not a primary key, skip the check */
7168-
if (indexStruct->indisprimary)
7168+
/*
7169+
* If the index is not a primary key or an index used as replica
7170+
* identity, skip the check.
7171+
*/
7172+
if (indexStruct->indisprimary || indexStruct->indisreplident)
71697173
{
71707174
/*
7171-
* Loop over each attribute in the primary key and see if it
7172-
* matches the to-be-altered attribute
7175+
* Loop over each attribute in the primary key or the index used
7176+
* as replica identity and see if it matches the to-be-altered
7177+
* attribute.
71737178
*/
71747179
for (i = 0; i < indexStruct->indnkeyatts; i++)
71757180
{
71767181
if (indexStruct->indkey.values[i] == attnum)
7177-
ereport(ERROR,
7178-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
7179-
errmsg("column \"%s\" is in a primary key",
7180-
colName)));
7182+
{
7183+
if (indexStruct->indisprimary)
7184+
ereport(ERROR,
7185+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
7186+
errmsg("column \"%s\" is in a primary key",
7187+
colName)));
7188+
else
7189+
ereport(ERROR,
7190+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
7191+
errmsg("column \"%s\" is in index used as replica identity",
7192+
colName)));
7193+
}
71817194
}
71827195
}
71837196

‎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