Add the notion of REPLICA IDENTITY for a table.

Pending patches for logical replication will use this to determine
which columns of a tuple ought to be considered as its candidate key.

Andres Freund, with minor, mostly cosmetic adjustments by me

4 files changed, 264 insertions, 1 deletions

diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
new file mode 100644
index 00000000000..60b866ad7ed
--- /dev/null
+++ b/src/test/regress/expected/replica_identity.out
@@ -0,0 +1,183 @@
+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 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 inelegible 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
+SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
+ relreplident 
+--------------
+ d
+(1 row)
+
+----
+-- Make sure index cases succeeed
+----
+-- 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   |                             Modifiers                              
+--------+---------+--------------------------------------------------------------------
+ id     | integer | not null default 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_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)
+    "test_replica_identity_hash" hash (nonkey)
+    "test_replica_identity_keyab" btree (keya, keyb)
+
+-- succeed, nondeferrable unique constraint over nonullable 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   |                             Modifiers                              
+--------+---------+--------------------------------------------------------------------
+ id     | integer | not null default 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_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)
+    "test_replica_identity_hash" hash (nonkey)
+    "test_replica_identity_keyab" 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   |                             Modifiers                              
+--------+---------+--------------------------------------------------------------------
+ id     | integer | not null default 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_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)
+    "test_replica_identity_hash" hash (nonkey)
+    "test_replica_identity_keyab" btree (keya, 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)
+
+DROP TABLE test_replica_identity;
+DROP TABLE test_replica_identity_othertable;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 1c1491c3f3e..5758b07fa42 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
 # ----------
 # Another group of parallel tests
 # ----------
-test: privileges security_label collate matview lock
+test: privileges security_label collate matview lock replica_identity
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index c4d451ab00c..78348f5f865 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -98,6 +98,7 @@ test: security_label
 test: collate
 test: matview
 test: lock
+test: replica_identity
 test: alter_generic
 test: misc
 test: psql
diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql
new file mode 100644
index 00000000000..9d2e9a6e7ed
--- /dev/null
+++ b/src/test/regress/sql/replica_identity.sql
@@ -0,0 +1,79 @@
+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 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;
+-- but 'none' for system tables
+SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass;
+SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass;
+
+----
+-- Make sure we detect inelegible indexes
+----
+
+-- fail, not unique
+ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab;
+-- fail, not a candidate key, nullable column
+ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey;
+-- fail, hash indexes cannot do uniqueness
+ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash;
+-- fail, expression index
+ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr;
+-- fail, partial index
+ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial;
+-- fail, not our index
+ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey;
+-- fail, deferrable
+ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer;
+
+SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
+
+----
+-- Make sure index cases succeeed
+----
+
+-- 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;
+\d test_replica_identity
+
+-- succeed, nondeferrable unique constraint over nonullable 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;
+\d test_replica_identity
+SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
+
+----
+-- 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;
+SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
+
+ALTER TABLE test_replica_identity REPLICA IDENTITY FULL;
+SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
+\d test_replica_identity
+ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING;
+SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
+
+DROP TABLE test_replica_identity;
+DROP TABLE test_replica_identity_othertable;