Hi,
We are building internal CDC tooling and have found it challenging to
identify which index is used as the replica identity for a publication. I
propose adding two new columns, replident and replidentidx, to the
pg_publication_tables catalog view so users can easily see this
configuration.
I have attached a patch with the implementation and updated regression
tests. Please let me know if you feel this is a valuable addition or if you
have any concerns with this approach.
Thanks,
Peter Travers
Engineer
linear.app
diff --git a/replica_identity_enhancements.patch b/replica_identity_enhancements.patch
new file mode 100644
index 0000000000..bbad40cdf9
--- /dev/null
+++ b/replica_identity_enhancements.patch
@@ -0,0 +1,199 @@
+diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
+index 8f129baec9..03cbf048d5 100644
+--- a/src/backend/catalog/system_views.sql
++++ b/src/backend/catalog/system_views.sql
+@@ -420,7 +420,9 @@ CREATE VIEW pg_publication_tables AS
+ WHERE a.attrelid = GPT.relid AND
+ a.attnum = ANY(GPT.attrs)
+ ) AS attnames,
+- pg_get_expr(GPT.qual, GPT.relid) AS rowfilter
++ pg_get_expr(GPT.qual, GPT.relid) AS rowfilter,
++ C.relreplident AS replident,
++ pg_get_replica_identity_index(C.oid) AS replidentidx
+ FROM pg_publication P,
+ LATERAL pg_get_publication_tables(P.pubname) GPT,
+ pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)
+diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
+index 29e54b214a..b350e61ed4 100644
+--- a/src/test/regress/expected/publication.out
++++ b/src/test/regress/expected/publication.out
+@@ -2100,52 +2100,52 @@ CREATE TABLE sch2.tbl1_part1 PARTITION OF sch1.tbl1 FOR VALUES FROM (1) to (10);
+ -- Schema publication that does not include the schema that has the parent table
+ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
+ SELECT * FROM pg_publication_tables;
+- pubname | schemaname | tablename | attnames | rowfilter
+----------+------------+------------+----------+-----------
+- pub | sch2 | tbl1_part1 | {a} |
++ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
++---------+------------+------------+----------+-----------+-----------+--------------
++ pub | sch2 | tbl1_part1 | {a} | | d |
+ (1 row)
+
+ DROP PUBLICATION pub;
+ -- Table publication that does not include the parent table
+ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
+ SELECT * FROM pg_publication_tables;
+- pubname | schemaname | tablename | attnames | rowfilter
+----------+------------+------------+----------+-----------
+- pub | sch2 | tbl1_part1 | {a} |
++ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
++---------+------------+------------+----------+-----------+-----------+--------------
++ pub | sch2 | tbl1_part1 | {a} | | d |
+ (1 row)
+
+ -- Table publication that includes both the parent table and the child table
+ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
+ SELECT * FROM pg_publication_tables;
+- pubname | schemaname | tablename | attnames | rowfilter
+----------+------------+-----------+----------+-----------
+- pub | sch1 | tbl1 | {a} |
++ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
++---------+------------+-----------+----------+-----------+-----------+--------------
++ pub | sch1 | tbl1 | {a} | | d |
+ (1 row)
+
+ DROP PUBLICATION pub;
+ -- Schema publication that does not include the schema that has the parent table
+ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
+ SELECT * FROM pg_publication_tables;
+- pubname | schemaname | tablename | attnames | rowfilter
+----------+------------+------------+----------+-----------
+- pub | sch2 | tbl1_part1 | {a} |
++ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
++---------+------------+------------+----------+-----------+-----------+--------------
++ pub | sch2 | tbl1_part1 | {a} | | d |
+ (1 row)
+
+ DROP PUBLICATION pub;
+ -- Table publication that does not include the parent table
+ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
+ SELECT * FROM pg_publication_tables;
+- pubname | schemaname | tablename | attnames | rowfilter
+----------+------------+------------+----------+-----------
+- pub | sch2 | tbl1_part1 | {a} |
++ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
++---------+------------+------------+----------+-----------+-----------+--------------
++ pub | sch2 | tbl1_part1 | {a} | | d |
+ (1 row)
+
+ -- Table publication that includes both the parent table and the child table
+ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
+ SELECT * FROM pg_publication_tables;
+- pubname | schemaname | tablename | attnames | rowfilter
+----------+------------+------------+----------+-----------
+- pub | sch2 | tbl1_part1 | {a} |
++ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
++---------+------------+------------+----------+-----------+-----------+--------------
++ pub | sch2 | tbl1_part1 | {a} | | d |
+ (1 row)
+
+ DROP PUBLICATION pub;
+@@ -2158,9 +2158,9 @@ CREATE TABLE sch1.tbl1_part3 (a int) PARTITION BY RANGE(a);
+ ALTER TABLE sch1.tbl1 ATTACH PARTITION sch1.tbl1_part3 FOR VALUES FROM (20) to (30);
+ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
+ SELECT * FROM pg_publication_tables;
+- pubname | schemaname | tablename | attnames | rowfilter
+----------+------------+-----------+----------+-----------
+- pub | sch1 | tbl1 | {a} |
++ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
++---------+------------+-----------+----------+-----------+-----------+--------------
++ pub | sch1 | tbl1 | {a} | | d |
+ (1 row)
+
+ RESET client_min_messages;
+@@ -2526,6 +2526,38 @@ DROP VIEW gpt_test_view;
+ DROP TABLE tbl_normal, tbl_parent, tbl_part1;
+ DROP SCHEMA gpt_test_sch CASCADE;
+ NOTICE: drop cascades to table gpt_test_sch.tbl_sch
++-- ======================================================
++-- Test replica identity in pg_publication_tables view
++-- ======================================================
++CREATE TABLE testpub_replident_d (a int PRIMARY KEY, b int);
++CREATE TABLE testpub_replident_f (a int, b int);
++ALTER TABLE testpub_replident_f REPLICA IDENTITY FULL;
++CREATE TABLE testpub_replident_i (a int not null, b int);
++CREATE UNIQUE INDEX testpub_replident_i_idx ON testpub_replident_i (a);
++ALTER TABLE testpub_replident_i REPLICA IDENTITY USING INDEX testpub_replident_i_idx;
++CREATE TABLE testpub_replident_n (a int, b int);
++ALTER TABLE testpub_replident_n REPLICA IDENTITY NOTHING;
++CREATE PUBLICATION testpub_replident FOR TABLE
++ testpub_replident_d,
++ testpub_replident_f,
++ testpub_replident_i,
++ testpub_replident_n;
++-- Check that replident and replidentidx are populated correctly
++SELECT tablename, replident, replidentidx::regclass
++FROM pg_publication_tables
++WHERE pubname = 'testpub_replident'
++ORDER BY tablename;
++ tablename | replident | replidentidx
++---------------------+-----------+--------------------------
++ testpub_replident_d | d | testpub_replident_d_pkey
++ testpub_replident_f | f |
++ testpub_replident_i | i | testpub_replident_i_idx
++ testpub_replident_n | n |
++(4 rows)
++
++-- Clean up
++DROP PUBLICATION testpub_replident;
++DROP TABLE testpub_replident_d, testpub_replident_f, testpub_replident_i, testpub_replident_n;
+ -- stage objects for pg_dump tests
+ CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int);
+ CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int);
+diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
+index a65a5bf0c4..bf05610275 100644
+--- a/src/test/regress/expected/rules.out
++++ b/src/test/regress/expected/rules.out
+@@ -1478,7 +1478,9 @@ pg_publication_tables| SELECT p.pubname,
+ ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
+ FROM pg_attribute a
+ WHERE ((a.attrelid = gpt.relid) AND (a.attnum = ANY ((gpt.attrs)::smallint[])))) AS attnames,
+- pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
++ pg_get_expr(gpt.qual, gpt.relid) AS rowfilter,
++ c.relreplident AS replident,
++ pg_get_replica_identity_index((c.oid)::regclass) AS replidentidx
+ FROM pg_publication p,
+ LATERAL pg_get_publication_tables(VARIADIC ARRAY[(p.pubname)::text]) gpt(pubid, relid, attrs, qual),
+ (pg_class c
+diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
+index 041e14a4de..e19f447047 100644
+--- a/src/test/regress/sql/publication.sql
++++ b/src/test/regress/sql/publication.sql
+@@ -1547,6 +1547,37 @@ DROP VIEW gpt_test_view;
+ DROP TABLE tbl_normal, tbl_parent, tbl_part1;
+ DROP SCHEMA gpt_test_sch CASCADE;
+
++-- ======================================================
++-- Test replica identity in pg_publication_tables view
++-- ======================================================
++CREATE TABLE testpub_replident_d (a int PRIMARY KEY, b int);
++
++CREATE TABLE testpub_replident_f (a int, b int);
++ALTER TABLE testpub_replident_f REPLICA IDENTITY FULL;
++
++CREATE TABLE testpub_replident_i (a int not null, b int);
++CREATE UNIQUE INDEX testpub_replident_i_idx ON testpub_replident_i (a);
++ALTER TABLE testpub_replident_i REPLICA IDENTITY USING INDEX testpub_replident_i_idx;
++
++CREATE TABLE testpub_replident_n (a int, b int);
++ALTER TABLE testpub_replident_n REPLICA IDENTITY NOTHING;
++
++CREATE PUBLICATION testpub_replident FOR TABLE
++ testpub_replident_d,
++ testpub_replident_f,
++ testpub_replident_i,
++ testpub_replident_n;
++
++-- Check that replident and replidentidx are populated correctly
++SELECT tablename, replident, replidentidx::regclass
++FROM pg_publication_tables
++WHERE pubname = 'testpub_replident'
++ORDER BY tablename;
++
++-- Clean up
++DROP PUBLICATION testpub_replident;
++DROP TABLE testpub_replident_d, testpub_replident_f, testpub_replident_i, testpub_replident_n;
++
+ -- stage objects for pg_dump tests
+ CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int);
+ CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8f129baec9..e5759594d8 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -420,7 +420,18 @@ CREATE VIEW pg_publication_tables AS
WHERE a.attrelid = GPT.relid AND
a.attnum = ANY(GPT.attrs)
) AS attnames,
- pg_get_expr(GPT.qual, GPT.relid) AS rowfilter
+ pg_get_expr(GPT.qual, GPT.relid) AS rowfilter,
+ C.relreplident AS replident,
+ (CASE
+ WHEN C.relreplident = 'd' THEN
+ (SELECT indexrelid FROM pg_index i
+ WHERE i.indrelid = C.oid AND i.indisprimary AND i.indisvalid AND i.indimmediate)
+ WHEN C.relreplident = 'i' THEN
+ (SELECT indexrelid FROM pg_index i
+ WHERE i.indrelid = C.oid AND i.indisreplident AND i.indisvalid AND i.indimmediate
+ AND i.indisunique AND i.indpred IS NULL)
+ ELSE NULL::oid
+ END) AS replidentidx
FROM pg_publication P,
LATERAL pg_get_publication_tables(P.pubname) GPT,
pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 29e54b214a..7d09a45ee0 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2100,52 +2100,52 @@ CREATE TABLE sch2.tbl1_part1 PARTITION OF sch1.tbl1 FOR VALUES FROM (1) to (10);
-- Schema publication that does not include the schema that has the parent table
CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
+---------+------------+------------+----------+-----------+-----------+--------------
+ pub | sch2 | tbl1_part1 | {a} | | d |
(1 row)
DROP PUBLICATION pub;
-- Table publication that does not include the parent table
CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
+---------+------------+------------+----------+-----------+-----------+--------------
+ pub | sch2 | tbl1_part1 | {a} | | d |
(1 row)
-- Table publication that includes both the parent table and the child table
ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
+---------+------------+-----------+----------+-----------+-----------+--------------
+ pub | sch1 | tbl1 | {a} | | d |
(1 row)
DROP PUBLICATION pub;
-- Schema publication that does not include the schema that has the parent table
CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
+---------+------------+------------+----------+-----------+-----------+--------------
+ pub | sch2 | tbl1_part1 | {a} | | d |
(1 row)
DROP PUBLICATION pub;
-- Table publication that does not include the parent table
CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
+---------+------------+------------+----------+-----------+-----------+--------------
+ pub | sch2 | tbl1_part1 | {a} | | d |
(1 row)
-- Table publication that includes both the parent table and the child table
ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
+---------+------------+------------+----------+-----------+-----------+--------------
+ pub | sch2 | tbl1_part1 | {a} | | d |
(1 row)
DROP PUBLICATION pub;
@@ -2158,9 +2158,9 @@ CREATE TABLE sch1.tbl1_part3 (a int) PARTITION BY RANGE(a);
ALTER TABLE sch1.tbl1 ATTACH PARTITION sch1.tbl1_part3 FOR VALUES FROM (20) to (30);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | replident | replidentidx
+---------+------------+-----------+----------+-----------+-----------+--------------
+ pub | sch1 | tbl1 | {a} | | d |
(1 row)
RESET client_min_messages;
@@ -2526,6 +2526,38 @@ DROP VIEW gpt_test_view;
DROP TABLE tbl_normal, tbl_parent, tbl_part1;
DROP SCHEMA gpt_test_sch CASCADE;
NOTICE: drop cascades to table gpt_test_sch.tbl_sch
+-- ======================================================
+-- Test replica identity in pg_publication_tables view
+-- ======================================================
+CREATE TABLE testpub_replident_d (a int PRIMARY KEY, b int);
+CREATE TABLE testpub_replident_f (a int, b int);
+ALTER TABLE testpub_replident_f REPLICA IDENTITY FULL;
+CREATE TABLE testpub_replident_i (a int not null, b int);
+CREATE UNIQUE INDEX testpub_replident_i_idx ON testpub_replident_i (a);
+ALTER TABLE testpub_replident_i REPLICA IDENTITY USING INDEX testpub_replident_i_idx;
+CREATE TABLE testpub_replident_n (a int, b int);
+ALTER TABLE testpub_replident_n REPLICA IDENTITY NOTHING;
+CREATE PUBLICATION testpub_replident FOR TABLE
+ testpub_replident_d,
+ testpub_replident_f,
+ testpub_replident_i,
+ testpub_replident_n;
+-- Check that replident and replidentidx are populated correctly
+SELECT tablename, replident, replidentidx::regclass
+FROM pg_publication_tables
+WHERE pubname = 'testpub_replident'
+ORDER BY tablename;
+ tablename | replident | replidentidx
+---------------------+-----------+--------------------------
+ testpub_replident_d | d | testpub_replident_d_pkey
+ testpub_replident_f | f |
+ testpub_replident_i | i | testpub_replident_i_idx
+ testpub_replident_n | n |
+(4 rows)
+
+-- Clean up
+DROP PUBLICATION testpub_replident;
+DROP TABLE testpub_replident_d, testpub_replident_f, testpub_replident_i, testpub_replident_n;
-- stage objects for pg_dump tests
CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int);
CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a65a5bf0c4..ebc824bb16 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1478,7 +1478,17 @@ pg_publication_tables| SELECT p.pubname,
( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
FROM pg_attribute a
WHERE ((a.attrelid = gpt.relid) AND (a.attnum = ANY ((gpt.attrs)::smallint[])))) AS attnames,
- pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
+ pg_get_expr(gpt.qual, gpt.relid) AS rowfilter,
+ c.relreplident AS replident,
+ CASE
+ WHEN (c.relreplident = 'd'::"char") THEN ( SELECT i.indexrelid
+ FROM pg_index i
+ WHERE ((i.indrelid = c.oid) AND i.indisprimary AND i.indisvalid AND i.indimmediate))
+ WHEN (c.relreplident = 'i'::"char") THEN ( SELECT i.indexrelid
+ FROM pg_index i
+ WHERE ((i.indrelid = c.oid) AND i.indisreplident AND i.indisvalid AND i.indimmediate AND i.indisunique AND (i.indpred IS NULL)))
+ ELSE NULL::oid
+ END AS replidentidx
FROM pg_publication p,
LATERAL pg_get_publication_tables(VARIADIC ARRAY[(p.pubname)::text]) gpt(pubid, relid, attrs, qual),
(pg_class c
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 041e14a4de..e19f447047 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1547,6 +1547,37 @@ DROP VIEW gpt_test_view;
DROP TABLE tbl_normal, tbl_parent, tbl_part1;
DROP SCHEMA gpt_test_sch CASCADE;
+-- ======================================================
+-- Test replica identity in pg_publication_tables view
+-- ======================================================
+CREATE TABLE testpub_replident_d (a int PRIMARY KEY, b int);
+
+CREATE TABLE testpub_replident_f (a int, b int);
+ALTER TABLE testpub_replident_f REPLICA IDENTITY FULL;
+
+CREATE TABLE testpub_replident_i (a int not null, b int);
+CREATE UNIQUE INDEX testpub_replident_i_idx ON testpub_replident_i (a);
+ALTER TABLE testpub_replident_i REPLICA IDENTITY USING INDEX testpub_replident_i_idx;
+
+CREATE TABLE testpub_replident_n (a int, b int);
+ALTER TABLE testpub_replident_n REPLICA IDENTITY NOTHING;
+
+CREATE PUBLICATION testpub_replident FOR TABLE
+ testpub_replident_d,
+ testpub_replident_f,
+ testpub_replident_i,
+ testpub_replident_n;
+
+-- Check that replident and replidentidx are populated correctly
+SELECT tablename, replident, replidentidx::regclass
+FROM pg_publication_tables
+WHERE pubname = 'testpub_replident'
+ORDER BY tablename;
+
+-- Clean up
+DROP PUBLICATION testpub_replident;
+DROP TABLE testpub_replident_d, testpub_replident_f, testpub_replident_i, testpub_replident_n;
+
-- stage objects for pg_dump tests
CREATE SCHEMA pubme CREATE TABLE t0 (c int, d int) CREATE TABLE t1 (c int);
CREATE SCHEMA pubme2 CREATE TABLE t0 (c int, d int);
