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; 9CREATE TABLE test_replica_identity_othertable (id serial primary key); 10CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); 11CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); 12CREATE UNIQUE INDEX test_replica_identity_oid_idx ON test_replica_identity (oid); 13CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); 14CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); 15CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); 16CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3'; 17-- default is 'd'/DEFAULT for user created tables 18SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 19 relreplident 20-------------- 21 d 22(1 row) 23 24-- but 'none' for system tables 25SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass; 26 relreplident 27-------------- 28 n 29(1 row) 30 31SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass; 32 relreplident 33-------------- 34 n 35(1 row) 36 37---- 38-- Make sure we detect ineligible indexes 39---- 40-- fail, not unique 41ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab; 42ERROR: cannot use non-unique index "test_replica_identity_keyab" as replica identity 43-- fail, not a candidate key, nullable column 44ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey; 45ERROR: index "test_replica_identity_nonkey" cannot be used as replica identity because column "nonkey" is nullable 46-- fail, hash indexes cannot do uniqueness 47ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash; 48ERROR: cannot use non-unique index "test_replica_identity_hash" as replica identity 49-- fail, expression index 50ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr; 51ERROR: cannot use expression index "test_replica_identity_expr" as replica identity 52-- fail, partial index 53ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial; 54ERROR: cannot use partial index "test_replica_identity_partial" as replica identity 55-- fail, not our index 56ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey; 57ERROR: "test_replica_identity_othertable_pkey" is not an index for table "test_replica_identity" 58-- fail, deferrable 59ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer; 60ERROR: cannot use non-immediate index "test_replica_identity_unique_defer" as replica identity 61SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 62 relreplident 63-------------- 64 d 65(1 row) 66 67---- 68-- Make sure index cases succeed 69---- 70-- succeed, primary key 71ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey; 72SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 73 relreplident 74-------------- 75 i 76(1 row) 77 78\d test_replica_identity 79 Table "public.test_replica_identity" 80 Column | Type | Collation | Nullable | Default 81--------+---------+-----------+----------+--------------------------------------------------- 82 id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) 83 keya | text | | not null | 84 keyb | text | | not null | 85 nonkey | text | | | 86Indexes: 87 "test_replica_identity_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY 88 "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) 89 "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) 90 "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) 91 "test_replica_identity_oid_idx" UNIQUE, btree (oid) 92 "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text 93 "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE 94 "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) 95 "test_replica_identity_hash" hash (nonkey) 96 "test_replica_identity_keyab" btree (keya, keyb) 97 98-- succeed, oid unique index 99ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_oid_idx; 100-- succeed, nondeferrable unique constraint over nonnullable cols 101ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; 102-- succeed unique index over nonnullable cols 103ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; 104ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; 105SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 106 relreplident 107-------------- 108 i 109(1 row) 110 111\d test_replica_identity 112 Table "public.test_replica_identity" 113 Column | Type | Collation | Nullable | Default 114--------+---------+-----------+----------+--------------------------------------------------- 115 id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) 116 keya | text | | not null | 117 keyb | text | | not null | 118 nonkey | text | | | 119Indexes: 120 "test_replica_identity_pkey" PRIMARY KEY, btree (id) 121 "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) 122 "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY 123 "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) 124 "test_replica_identity_oid_idx" UNIQUE, btree (oid) 125 "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text 126 "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE 127 "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) 128 "test_replica_identity_hash" hash (nonkey) 129 "test_replica_identity_keyab" btree (keya, keyb) 130 131SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; 132 count 133------- 134 1 135(1 row) 136 137---- 138-- Make sure non index cases work 139---- 140ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT; 141SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 142 relreplident 143-------------- 144 d 145(1 row) 146 147SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; 148 count 149------- 150 0 151(1 row) 152 153ALTER TABLE test_replica_identity REPLICA IDENTITY FULL; 154SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 155 relreplident 156-------------- 157 f 158(1 row) 159 160\d+ test_replica_identity 161 Table "public.test_replica_identity" 162 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 163--------+---------+-----------+----------+---------------------------------------------------+----------+--------------+------------- 164 id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | | 165 keya | text | | not null | | extended | | 166 keyb | text | | not null | | extended | | 167 nonkey | text | | | | extended | | 168Indexes: 169 "test_replica_identity_pkey" PRIMARY KEY, btree (id) 170 "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) 171 "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) 172 "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) 173 "test_replica_identity_oid_idx" UNIQUE, btree (oid) 174 "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text 175 "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE 176 "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) 177 "test_replica_identity_hash" hash (nonkey) 178 "test_replica_identity_keyab" btree (keya, keyb) 179Replica Identity: FULL 180Has OIDs: yes 181 182ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; 183SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; 184 relreplident 185-------------- 186 n 187(1 row) 188 189--- 190-- Test that ALTER TABLE rewrite preserves nondefault replica identity 191--- 192-- constraint variant 193CREATE TABLE test_replica_identity2 (id int UNIQUE NOT NULL); 194ALTER TABLE test_replica_identity2 REPLICA IDENTITY USING INDEX test_replica_identity2_id_key; 195\d test_replica_identity2 196 Table "public.test_replica_identity2" 197 Column | Type | Collation | Nullable | Default 198--------+---------+-----------+----------+--------- 199 id | integer | | not null | 200Indexes: 201 "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY 202 203ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint; 204\d test_replica_identity2 205 Table "public.test_replica_identity2" 206 Column | Type | Collation | Nullable | Default 207--------+--------+-----------+----------+--------- 208 id | bigint | | not null | 209Indexes: 210 "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY 211 212-- straight index variant 213CREATE TABLE test_replica_identity3 (id int NOT NULL); 214CREATE UNIQUE INDEX test_replica_identity3_id_key ON test_replica_identity3 (id); 215ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_identity3_id_key; 216\d test_replica_identity3 217 Table "public.test_replica_identity3" 218 Column | Type | Collation | Nullable | Default 219--------+---------+-----------+----------+--------- 220 id | integer | | not null | 221Indexes: 222 "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY 223 224ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint; 225\d test_replica_identity3 226 Table "public.test_replica_identity3" 227 Column | Type | Collation | Nullable | Default 228--------+--------+-----------+----------+--------- 229 id | bigint | | not null | 230Indexes: 231 "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY 232 233DROP TABLE test_replica_identity; 234DROP TABLE test_replica_identity2; 235DROP TABLE test_replica_identity3; 236DROP TABLE test_replica_identity_othertable; 237