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) ; 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_nonkey ON test_replica_identity (keya, nonkey); 15CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); 16CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); 17CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3'; 18 19-- default is 'd'/DEFAULT for user created tables 20SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 21-- but 'none' for system tables 22SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass; 23SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass; 24 25---- 26-- Make sure we detect ineligible indexes 27---- 28 29-- fail, not unique 30ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab; 31-- fail, not a candidate key, nullable column 32ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey; 33-- fail, hash indexes cannot do uniqueness 34ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash; 35-- fail, expression index 36ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr; 37-- fail, partial index 38ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial; 39-- fail, not our index 40ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey; 41-- fail, deferrable 42ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer; 43 44SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 45 46---- 47-- Make sure index cases succeed 48---- 49 50-- succeed, primary key 51ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey; 52SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 53\d test_replica_identity 54 55-- succeed, nondeferrable unique constraint over nonnullable cols 56ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; 57 58-- succeed unique index over nonnullable cols 59ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; 60ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; 61SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 62\d test_replica_identity 63SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; 64 65---- 66-- Make sure non index cases work 67---- 68ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT; 69SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 70SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; 71 72ALTER TABLE test_replica_identity REPLICA IDENTITY FULL; 73SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 74\d+ test_replica_identity 75ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; 76SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 77 78DROP TABLE test_replica_identity; 79DROP TABLE test_replica_identity_othertable; 80