1 CREATE TABLE test_replica_identity (
6 CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE,
7 CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb)
9 CREATE TABLE test_replica_identity_othertable (id serial primary key);
10 CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb);
11 CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb);
12 CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey);
13 CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey);
14 CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3));
15 CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3';
16 -- default is 'd'/DEFAULT for user created tables
17 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
23 -- but 'none' for system tables
24 SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass;
30 SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass;
37 -- Make sure we detect ineligible indexes
40 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab;
41 ERROR: cannot use non-unique index "test_replica_identity_keyab" as replica identity
42 -- fail, not a candidate key, nullable column
43 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey;
44 ERROR: index "test_replica_identity_nonkey" cannot be used as replica identity because column "nonkey" is nullable
45 -- fail, hash indexes cannot do uniqueness
46 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash;
47 ERROR: cannot use non-unique index "test_replica_identity_hash" as replica identity
48 -- fail, expression index
49 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr;
50 ERROR: cannot use expression index "test_replica_identity_expr" as replica identity
51 -- fail, partial index
52 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial;
53 ERROR: cannot use partial index "test_replica_identity_partial" as replica identity
54 -- fail, not our index
55 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey;
56 ERROR: "test_replica_identity_othertable_pkey" is not an index for table "test_replica_identity"
58 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer;
59 ERROR: cannot use non-immediate index "test_replica_identity_unique_defer" as replica identity
60 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
67 -- Make sure index cases succeed
69 -- succeed, primary key
70 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey;
71 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
77 \d test_replica_identity
78 Table "public.test_replica_identity"
79 Column | Type | Collation | Nullable | Default
80 --------+---------+-----------+----------+---------------------------------------------------
81 id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass)
82 keya | text | | not null |
83 keyb | text | | not null |
86 "test_replica_identity_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY
87 "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
88 "test_replica_identity_hash" hash (nonkey)
89 "test_replica_identity_keyab" btree (keya, keyb)
90 "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb)
91 "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
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)
96 -- succeed, nondeferrable unique constraint over nonnullable cols
97 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer;
98 -- succeed unique index over nonnullable cols
99 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key;
100 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key;
101 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
107 \d test_replica_identity
108 Table "public.test_replica_identity"
109 Column | Type | Collation | Nullable | Default
110 --------+---------+-----------+----------+---------------------------------------------------
111 id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass)
112 keya | text | | not null |
113 keyb | text | | not null |
116 "test_replica_identity_pkey" PRIMARY KEY, btree (id)
117 "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
118 "test_replica_identity_hash" hash (nonkey)
119 "test_replica_identity_keyab" btree (keya, keyb)
120 "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY
121 "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
122 "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
123 "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
124 "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
126 SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
133 -- Make sure non index cases work
135 ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT;
136 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
142 SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
148 ALTER TABLE test_replica_identity REPLICA IDENTITY FULL;
149 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
155 \d+ test_replica_identity
156 Table "public.test_replica_identity"
157 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
158 --------+---------+-----------+----------+---------------------------------------------------+----------+--------------+-------------
159 id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | |
160 keya | text | | not null | | extended | |
161 keyb | text | | not null | | extended | |
162 nonkey | text | | | | extended | |
164 "test_replica_identity_pkey" PRIMARY KEY, btree (id)
165 "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
166 "test_replica_identity_hash" hash (nonkey)
167 "test_replica_identity_keyab" btree (keya, keyb)
168 "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb)
169 "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
170 "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
171 "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
172 "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
173 Replica Identity: FULL
175 ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING;
176 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
183 -- Test that ALTER TABLE rewrite preserves nondefault replica identity
185 -- constraint variant
186 CREATE TABLE test_replica_identity2 (id int UNIQUE NOT NULL);
187 ALTER TABLE test_replica_identity2 REPLICA IDENTITY USING INDEX test_replica_identity2_id_key;
188 \d test_replica_identity2
189 Table "public.test_replica_identity2"
190 Column | Type | Collation | Nullable | Default
191 --------+---------+-----------+----------+---------
192 id | integer | | not null |
194 "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY
196 ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint;
197 \d test_replica_identity2
198 Table "public.test_replica_identity2"
199 Column | Type | Collation | Nullable | Default
200 --------+--------+-----------+----------+---------
201 id | bigint | | not null |
203 "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY
205 -- straight index variant
206 CREATE TABLE test_replica_identity3 (id int NOT NULL);
207 CREATE UNIQUE INDEX test_replica_identity3_id_key ON test_replica_identity3 (id);
208 ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_identity3_id_key;
209 \d test_replica_identity3
210 Table "public.test_replica_identity3"
211 Column | Type | Collation | Nullable | Default
212 --------+---------+-----------+----------+---------
213 id | integer | | not null |
215 "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY
217 ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint;
218 \d test_replica_identity3
219 Table "public.test_replica_identity3"
220 Column | Type | Collation | Nullable | Default
221 --------+--------+-----------+----------+---------
222 id | bigint | | not null |
224 "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY
226 DROP TABLE test_replica_identity;
227 DROP TABLE test_replica_identity2;
228 DROP TABLE test_replica_identity3;
229 DROP TABLE test_replica_identity_othertable;