1CREATE TABLE test_replica_identity ( 2 id serial primary key, 3 keya text not null, 4 keyb text not null, 5 nonkey text, 6 CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, 7 CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) 8) WITH OIDS; 9 10CREATE TABLE test_replica_identity_othertable (id serial primary key); 11 12CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); 13CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); 14CREATE UNIQUE INDEX test_replica_identity_oid_idx ON test_replica_identity (oid); 15CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); 16CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); 17CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); 18CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3'; 19 20-- default is 'd'/DEFAULT for user created tables 21SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 22-- but 'none' for system tables 23SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass; 24SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass; 25 26---- 27-- Make sure we detect ineligible indexes 28---- 29 30-- fail, not unique 31ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab; 32-- fail, not a candidate key, nullable column 33ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey; 34-- fail, hash indexes cannot do uniqueness 35ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash; 36-- fail, expression index 37ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr; 38-- fail, partial index 39ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial; 40-- fail, not our index 41ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey; 42-- fail, deferrable 43ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer; 44 45SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 46 47---- 48-- Make sure index cases succeed 49---- 50 51-- succeed, primary key 52ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey; 53SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 54\d test_replica_identity 55 56-- succeed, oid unique index 57ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_oid_idx; 58 59-- succeed, nondeferrable unique constraint over nonnullable cols 60ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; 61 62-- succeed unique index over nonnullable cols 63ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; 64ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; 65SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 66\d test_replica_identity 67SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; 68 69---- 70-- Make sure non index cases work 71---- 72ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT; 73SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 74SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; 75 76ALTER TABLE test_replica_identity REPLICA IDENTITY FULL; 77SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 78\d+ test_replica_identity 79ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; 80SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 81 82--- 83-- Test that ALTER TABLE rewrite preserves nondefault replica identity 84--- 85 86-- constraint variant 87CREATE TABLE test_replica_identity2 (id int UNIQUE NOT NULL); 88ALTER TABLE test_replica_identity2 REPLICA IDENTITY USING INDEX test_replica_identity2_id_key; 89\d test_replica_identity2 90ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint; 91\d test_replica_identity2 92 93-- straight index variant 94CREATE TABLE test_replica_identity3 (id int NOT NULL); 95CREATE UNIQUE INDEX test_replica_identity3_id_key ON test_replica_identity3 (id); 96ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_identity3_id_key; 97\d test_replica_identity3 98ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint; 99\d test_replica_identity3 100 101DROP TABLE test_replica_identity; 102DROP TABLE test_replica_identity2; 103DROP TABLE test_replica_identity3; 104DROP TABLE test_replica_identity_othertable; 105