From 8f1911d5e6d5a1e62c860ddb040d664b01c6415c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 15 Apr 2016 12:11:27 -0400 Subject: [PATCH] Fix possible crash in ALTER TABLE ... REPLICA IDENTITY USING INDEX. Careless coding added by commit 07cacba983ef79be could result in a crash or a bizarre error message if someone tried to select an index on the OID column as the replica identity index for a table. Back-patch to 9.4 where the feature was introduced. Discussion: CAKJS1f8TQYgTRDyF1_u9PVCKWRWz+DkieH=U7954HeHVPJKaKg@mail.gmail.com David Rowley --- src/backend/commands/tablecmds.c | 18 ++++++++++++++---- src/test/regress/expected/replica_identity.out | 9 ++++++++- src/test/regress/sql/replica_identity.sql | 6 +++++- 3 files changed, 27 insertions(+), 6 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index eaf76d2b90..45a5144643 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11052,10 +11052,20 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode int16 attno = indexRel->rd_index->indkey.values[key]; Form_pg_attribute attr; - /* Of the system columns, only oid is indexable. */ - if (attno <= 0 && attno != ObjectIdAttributeNumber) - elog(ERROR, "internal column %u in unique index \"%s\"", - attno, RelationGetRelationName(indexRel)); + /* Allow OID column to be indexed; it's certainly not nullable */ + if (attno == ObjectIdAttributeNumber) + continue; + + /* + * Reject any other system columns. (Going forward, we'll disallow + * indexes containing such columns in the first place, but they might + * exist in older branches.) + */ + if (attno <= 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("index \"%s\" cannot be used as replica identity because column %d is a system column", + RelationGetRelationName(indexRel), attno))); attr = rel->rd_att->attrs[attno - 1]; if (!attr->attnotnull) diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index 60d9a424a1..39a60a5619 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -5,10 +5,11 @@ CREATE TABLE test_replica_identity ( nonkey text, CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) -); +) WITH OIDS; CREATE TABLE test_replica_identity_othertable (id serial primary key); CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_oid_idx ON test_replica_identity (oid); CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); WARNING: hash indexes are not WAL-logged and their use is discouraged @@ -88,12 +89,15 @@ Indexes: "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_oid_idx" UNIQUE, btree (oid) "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) "test_replica_identity_hash" hash (nonkey) "test_replica_identity_keyab" btree (keya, keyb) +-- succeed, oid unique index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_oid_idx; -- succeed, nondeferrable unique constraint over nonullable cols ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; -- succeed unique index over nonnullable cols @@ -118,6 +122,7 @@ Indexes: "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_oid_idx" UNIQUE, btree (oid) "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) @@ -166,12 +171,14 @@ Indexes: "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_oid_idx" UNIQUE, btree (oid) "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) "test_replica_identity_hash" hash (nonkey) "test_replica_identity_keyab" btree (keya, keyb) Replica Identity: FULL +Has OIDs: yes ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql index 20b682685e..68824a3aa7 100644 --- a/src/test/regress/sql/replica_identity.sql +++ b/src/test/regress/sql/replica_identity.sql @@ -5,12 +5,13 @@ CREATE TABLE test_replica_identity ( nonkey text, CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) -); +) WITH OIDS; CREATE TABLE test_replica_identity_othertable (id serial primary key); CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_oid_idx ON test_replica_identity (oid); CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); @@ -52,6 +53,9 @@ ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_iden SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; \d test_replica_identity +-- succeed, oid unique index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_oid_idx; + -- succeed, nondeferrable unique constraint over nonullable cols ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; -- 2.40.0