/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/replica_identity.out
324 строки
15 KB
Álvaro Herrera
Reject REPLICA IDENTITY USING INDEX on column with invalid NOT NULL
12 авг 2026, 20:15
Не верифицирован
12 авг 2026, 20:15
bdaad78
Код
Авторство
О чём код?
CREATE TABLE test_replica_identity ( id serial primary key, keya text not null, keyb text not null, nonkey text, CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) ) ; CREATE TABLE test_replica_identity_othertable (id serial primary key); CREATE TABLE test_replica_identity_t3 (id serial constraint pk primary key deferrable); 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_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)); CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3'; -- default is 'd'/DEFAULT for user created tables SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; relreplident -------------- d (1 row) -- but 'none' for system tables SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass; relreplident -------------- n (1 row) SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass; relreplident -------------- n (1 row) ---- -- Make sure we detect ineligible indexes ---- -- fail, not unique ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab; ERROR: cannot use non-unique index "test_replica_identity_keyab" as replica identity -- fail, not a candidate key, nullable column ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey; ERROR: index "test_replica_identity_nonkey" cannot be used as replica identity because column "nonkey" is nullable -- fail, hash indexes cannot do uniqueness ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash; ERROR: cannot use non-unique index "test_replica_identity_hash" as replica identity -- fail, expression index ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr; ERROR: cannot use expression index "test_replica_identity_expr" as replica identity -- fail, partial index ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial; ERROR: cannot use partial index "test_replica_identity_partial" as replica identity -- fail, not our index ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey; ERROR: "test_replica_identity_othertable_pkey" is not an index for table "test_replica_identity" -- fail, deferrable ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer; ERROR: cannot use non-immediate index "test_replica_identity_unique_defer" as replica identity -- fail, deferrable ALTER TABLE test_replica_identity_t3 REPLICA IDENTITY USING INDEX pk; ERROR: cannot use non-immediate index "pk" as replica identity SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; relreplident -------------- d (1 row) ---- -- Make sure index cases succeed ---- -- succeed, primary key ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; relreplident -------------- i (1 row) \d test_replica_identity Table "public.test_replica_identity" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------------------------------------------------- id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) keya | text | | not null | keyb | text | | not null | nonkey | text | | | Indexes: "test_replica_identity_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_hash" hash (nonkey) "test_replica_identity_keyab" btree (keya, keyb) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) "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) -- succeed, nondeferrable unique constraint over nonnullable cols ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; -- succeed unique index over nonnullable cols ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; relreplident -------------- i (1 row) \d test_replica_identity Table "public.test_replica_identity" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------------------------------------------------- id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) keya | text | | not null | keyb | text | | not null | nonkey | text | | | Indexes: "test_replica_identity_pkey" PRIMARY KEY, btree (id) "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_hash" hash (nonkey) "test_replica_identity_keyab" btree (keya, keyb) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) "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) SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; count ------- 1 (1 row) ---- -- Make sure non index cases work ---- ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; relreplident -------------- d (1 row) SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; count ------- 0 (1 row) ALTER TABLE test_replica_identity REPLICA IDENTITY FULL; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; relreplident -------------- f (1 row) \d+ test_replica_identity Table "public.test_replica_identity" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------------------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | | keya | text | | not null | | extended | | keyb | text | | not null | | extended | | nonkey | text | | | | extended | | Indexes: "test_replica_identity_pkey" PRIMARY KEY, btree (id) "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) "test_replica_identity_hash" hash (nonkey) "test_replica_identity_keyab" btree (keya, keyb) "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) "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) Not-null constraints: "test_replica_identity_id_not_null" NOT NULL "id" "test_replica_identity_keya_not_null" NOT NULL "keya" "test_replica_identity_keyb_not_null" NOT NULL "keyb" Replica Identity: FULL ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; relreplident -------------- n (1 row) --- -- Test that ALTER TABLE rewrite preserves nondefault replica identity --- -- constraint variant CREATE TABLE test_replica_identity2 (id int UNIQUE NOT NULL); ALTER TABLE test_replica_identity2 REPLICA IDENTITY USING INDEX test_replica_identity2_id_key; \d test_replica_identity2 Table "public.test_replica_identity2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | Indexes: "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint; \d test_replica_identity2 Table "public.test_replica_identity2" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+--------- id | bigint | | not null | Indexes: "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY -- straight index variant CREATE TABLE test_replica_identity3 (id int NOT NULL); CREATE UNIQUE INDEX test_replica_identity3_id_key ON test_replica_identity3 (id); ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_identity3_id_key; \d test_replica_identity3 Table "public.test_replica_identity3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | Indexes: "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint; \d test_replica_identity3 Table "public.test_replica_identity3" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+--------- id | bigint | | not null | Indexes: "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY -- ALTER TABLE DROP NOT NULL is not allowed for columns part of an index -- used as replica identity. ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL; ERROR: column "id" is in index used as replica identity -- but it's OK when the identity is FULL ALTER TABLE test_replica_identity3 REPLICA IDENTITY FULL; ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL; -- -- Test that replica identity can be set on an index that's not yet valid. -- (This matches the way pg_dump will try to dump a partitioned table.) -- CREATE TABLE test_replica_identity4(id integer NOT NULL) PARTITION BY LIST (id); CREATE TABLE test_replica_identity4_1(id integer NOT NULL); ALTER TABLE ONLY test_replica_identity4 ATTACH PARTITION test_replica_identity4_1 FOR VALUES IN (1); ALTER TABLE ONLY test_replica_identity4 ADD CONSTRAINT test_replica_identity4_pkey PRIMARY KEY (id); ALTER TABLE ONLY test_replica_identity4 REPLICA IDENTITY USING INDEX test_replica_identity4_pkey; ALTER TABLE ONLY test_replica_identity4_1 ADD CONSTRAINT test_replica_identity4_1_pkey PRIMARY KEY (id); \d+ test_replica_identity4 Partitioned table "public.test_replica_identity4" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | not null | | plain | | Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) INVALID REPLICA IDENTITY Not-null constraints: "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) ALTER INDEX test_replica_identity4_pkey ATTACH PARTITION test_replica_identity4_1_pkey; \d+ test_replica_identity4 Partitioned table "public.test_replica_identity4" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | not null | | plain | | Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY Not-null constraints: "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) -- Dropping the primary key is not allowed if that would leave the replica -- identity as nullable CREATE TABLE test_replica_identity5 (a int not null, b int, c int, PRIMARY KEY (b, c)); CREATE UNIQUE INDEX test_replica_identity5_a_b_key ON test_replica_identity5 (a, b); ALTER TABLE test_replica_identity5 REPLICA IDENTITY USING INDEX test_replica_identity5_a_b_key; ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey; ALTER TABLE test_replica_identity5 ALTER b SET NOT NULL; ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey; ERROR: constraint "test_replica_identity5_pkey" of relation "test_replica_identity5" does not exist ALTER TABLE test_replica_identity5 ALTER b DROP NOT NULL; ERROR: column "b" is in index used as replica identity -- An invalid (NOT VALID) not-null constraint sets attnotnull but does not -- prove the column null-free, so the index must not be accepted as replica -- identity until the constraint is validated. CREATE TABLE test_replica_identity6 (id int); INSERT INTO test_replica_identity6 VALUES (1), (NULL); ALTER TABLE test_replica_identity6 ADD CONSTRAINT id_nn NOT NULL id NOT VALID; CREATE UNIQUE INDEX test_replica_identity6_idx ON test_replica_identity6 (id); -- should fail ALTER TABLE test_replica_identity6 REPLICA IDENTITY USING INDEX test_replica_identity6_idx; ERROR: cannot use index "test_replica_identity6_idx" as replica identity DETAIL: The constraint "id_nn" on column "id" is marked NOT VALID. HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT. -- after removing offending row and validating, it should succeed DELETE FROM test_replica_identity6 WHERE id IS NULL; ALTER TABLE test_replica_identity6 VALIDATE CONSTRAINT id_nn; ALTER TABLE test_replica_identity6 REPLICA IDENTITY USING INDEX test_replica_identity6_idx; SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity6'::regclass; relreplident -------------- i (1 row) DROP TABLE test_replica_identity; DROP TABLE test_replica_identity2; DROP TABLE test_replica_identity3; DROP TABLE test_replica_identity4; DROP TABLE test_replica_identity5; DROP TABLE test_replica_identity6; DROP TABLE test_replica_identity_othertable; DROP TABLE test_replica_identity_t3;