On Mon, Jun 30, 2025 at 5:17 PM Amit Kapila <amit.kapil...@gmail.com> wrote:
>
> On Mon, Jun 30, 2025 at 3:44 PM Ashutosh Bapat
> <ashutosh.bapat....@gmail.com> wrote:
> >
> > Hi All,
> >
> > The commit message in the patch says it all, but let me repeat it here.
> >
>
> You forgot to attach the patch.
Sorry. Here it is
>
> > When debugging issues with logical replication, replica identity
> > property of tables in publication is often useful, for example, to
> > determine the amount of data logged for an UPDATE or DELETE on a given
> > table.
> >
>
> I think it can help to determine what is logged for the DELETE or
> UPDATE operation, but not the exact amount of data. Can you please
> explain with an example how such information can help with debugging?
No. The change itself won't tell the amount of data that will be
logged. But given a publication it will tell what all tables being
published by that publication are using replica identity full - which
causes more columns to be logged compared to replica identity default
or index.
>
> > Given a set of publications that a WAL sender is using,
> > pg_publication_tables can be used to get the list of tables whose
> > changes will be replicated including the columns of those tables and
> > row
> > filters. But the replica identity of those tables needs to be
> > separately found out by querying pg_class or joining pg_class with
> > pg_publication_tables on schemaname and relname. Adding the replica
> > identity column to pg_publication_tables avoids this extra step.
> >
> > The replica identity of a given table is not a property of
> > publication, per say, so it's arguable whether it should be included
> > in pg_publication_tables or not.
> >
>
> Right, discussing the use case a bit more might help us to find if
> this is the right place to add 'replica identity' information.
In our case, we had multiple replication slots, each with a different
publication. One of the slots was lagging because of the amount of
data being sent through the slot. For that we wanted to know which
tables are being published through the corresponding publication and
what's the replica identity of each of the tables.
pg_publication_tables gave us the list of tables but in order to get
its replica identity we needed to join it with pg_class again.
pg_publication_tables already joins pg_class. Exposing replica
identity through pg_publication_tables makes it more convenient to get
all the information related to a tables replication through that
publication in a single line without much code change or run time
cost.
--
Best Wishes,
Ashutosh Bapat
From 534135e55ea228a42f735f9dd3cc3bead9b12f70 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat....@gmail.com>
Date: Fri, 27 Jun 2025 12:25:31 +0530
Subject: [PATCH] Report replica identity property of tables in
pg_publication_tables
When debugging issues with logical replication, replica identity
property of tables in publication is often useful, for example, to
determine the amount of data logged for an UPDATE or DELETE on a table.
Given a set of publications that a WAL sender is using,
pg_publication_tables can be used to get the list of tables whose
changes will be replicated including the columns of those tables and row
filters. But the replica identity of those tables needs to be separately
found out by querying pg_class or joining pg_class with
pg_publication_tables. Adding replica identity column to
pg_publication_tables avoids this extra step.
The replica identity for a given table does not change with publication
so the information will be repeated as many times the number of
publications a given table is part of. But the repetition is worth the
convenience.
Ashutosh Bapat
---
doc/src/sgml/system-views.sgml | 9 +++
src/backend/catalog/system_views.sql | 9 ++-
src/test/regress/expected/publication.out | 96 ++++++++++++++++++-----
src/test/regress/expected/rules.out | 9 ++-
src/test/regress/sql/publication.sql | 9 +++
5 files changed, 109 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 986ae1f543d..8ec1b7ba499 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -2558,6 +2558,15 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
Expression for the table's publication qualifying condition
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>replica_identity</structfield> <type>text</type>
+ </para>
+ <para>
+ Replica identity setting of the table.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 08f780a2e63..34fca1bba54 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -388,7 +388,14 @@ 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,
+ case C.relreplident
+ when 'd' then 'default'
+ when 'n' then 'nothing'
+ when 'f' then 'full'
+ when 'i' then 'index'
+ else NULL
+ end as replica_identity
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 3a2eacd793f..2ecad5cfacd 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -588,21 +588,45 @@ ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (d > 99);
UPDATE rf_tbl_abcd_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_pk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+----------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_pk | {a,b,c,d} | (d > 99) | default
+(1 row)
+
-- 1b. REPLICA IDENTITY is DEFAULT and table has no PK
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- fail - "a" is not part of REPLICA IDENTITY
UPDATE rf_tbl_abcd_nopk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_nopk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+------------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_nopk | {a,b,c,d} | (a > 99) | default
+(1 row)
+
-- Case 2. REPLICA IDENTITY FULL
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL;
ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
-- ok - "c" is in REPLICA IDENTITY now even though not in PK
UPDATE rf_tbl_abcd_pk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+----------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_pk | {a,b,c,d} | (c > 99) | full
+(1 row)
+
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- ok - "a" is in REPLICA IDENTITY now
UPDATE rf_tbl_abcd_nopk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+------------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_nopk | {a,b,c,d} | (a > 99) | full
+(1 row)
+
-- Case 3. REPLICA IDENTITY NOTHING
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING;
ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING;
@@ -616,11 +640,23 @@ ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
UPDATE rf_tbl_abcd_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_pk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+----------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_pk | {a,b,c,d} | (c > 99) | nothing
+(1 row)
+
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- fail - "a" is not in REPLICA IDENTITY NOTHING
UPDATE rf_tbl_abcd_nopk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_nopk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+------------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_nopk | {a,b,c,d} | (a > 99) | nothing
+(1 row)
+
-- Case 4. REPLICA IDENTITY INDEX
ALTER TABLE rf_tbl_abcd_pk ALTER COLUMN c SET NOT NULL;
CREATE UNIQUE INDEX idx_abcd_pk_c ON rf_tbl_abcd_pk(c);
@@ -636,6 +672,12 @@ DETAIL: Column used in the publication WHERE expression is not part of the repl
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
-- ok - "c" is not in PK but it is part of REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_pk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+----------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_pk | {a,b,c,d} | (c > 99) | index
+(1 row)
+
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- fail - "a" is not in REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_nopk SET a = 1;
@@ -644,6 +686,12 @@ DETAIL: Column used in the publication WHERE expression is not part of the repl
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (c > 99);
-- ok - "c" is part of REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_nopk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+------------------+-----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_nopk | {a,b,c,d} | (c > 99) | index
+(1 row)
+
-- Tests for partitioned table
-- set PUBLISH_VIA_PARTITION_ROOT to false and test row filter for partitioned
-- table
@@ -690,6 +738,12 @@ ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (b > 99);
UPDATE rf_tbl_abcd_part_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_part_pk_1"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
+ pubname | schemaname | tablename | attnames | rowfilter | replica_identity
+----------+------------+---------------------+----------+-----------+------------------
+ testpub6 | public | rf_tbl_abcd_part_pk | {a,b} | (b > 99) | default
+(1 row)
+
DROP PUBLICATION testpub6;
DROP TABLE rf_tbl_abcd_pk;
DROP TABLE rf_tbl_abcd_nopk;
@@ -1774,52 +1828,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 | replica_identity
+---------+------------+------------+----------+-----------+------------------
+ pub | sch2 | tbl1_part1 | {a} | | default
(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 | replica_identity
+---------+------------+------------+----------+-----------+------------------
+ pub | sch2 | tbl1_part1 | {a} | | default
(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 | replica_identity
+---------+------------+-----------+----------+-----------+------------------
+ pub | sch1 | tbl1 | {a} | | default
(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 | replica_identity
+---------+------------+------------+----------+-----------+------------------
+ pub | sch2 | tbl1_part1 | {a} | | default
(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 | replica_identity
+---------+------------+------------+----------+-----------+------------------
+ pub | sch2 | tbl1_part1 | {a} | | default
(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 | replica_identity
+---------+------------+------------+----------+-----------+------------------
+ pub | sch2 | tbl1_part1 | {a} | | default
(1 row)
DROP PUBLICATION pub;
@@ -1832,9 +1886,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 | replica_identity
+---------+------------+-----------+----------+-----------+------------------
+ pub | sch1 | tbl1 | {a} | | default
(1 row)
RESET client_min_messages;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6cf828ca8d0..682408f3598 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1464,7 +1464,14 @@ 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,
+ CASE c.relreplident
+ WHEN 'd'::"char" THEN 'default'::text
+ WHEN 'n'::"char" THEN 'nothing'::text
+ WHEN 'f'::"char" THEN 'full'::text
+ WHEN 'i'::"char" THEN 'index'::text
+ ELSE NULL::text
+ END AS replica_identity
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 c9e309190df..d78a4b1cbf7 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -317,10 +317,12 @@ UPDATE rf_tbl_abcd_pk SET a = 1;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (d > 99);
-- fail - "d" is not part of the PK
UPDATE rf_tbl_abcd_pk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
-- 1b. REPLICA IDENTITY is DEFAULT and table has no PK
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- fail - "a" is not part of REPLICA IDENTITY
UPDATE rf_tbl_abcd_nopk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
-- Case 2. REPLICA IDENTITY FULL
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL;
@@ -328,9 +330,11 @@ ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
-- ok - "c" is in REPLICA IDENTITY now even though not in PK
UPDATE rf_tbl_abcd_pk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- ok - "a" is in REPLICA IDENTITY now
UPDATE rf_tbl_abcd_nopk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
-- Case 3. REPLICA IDENTITY NOTHING
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING;
@@ -341,9 +345,11 @@ UPDATE rf_tbl_abcd_pk SET a = 1;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
-- fail - "c" is not in PK and not in REPLICA IDENTITY NOTHING
UPDATE rf_tbl_abcd_pk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- fail - "a" is not in REPLICA IDENTITY NOTHING
UPDATE rf_tbl_abcd_nopk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
-- Case 4. REPLICA IDENTITY INDEX
ALTER TABLE rf_tbl_abcd_pk ALTER COLUMN c SET NOT NULL;
@@ -358,12 +364,14 @@ UPDATE rf_tbl_abcd_pk SET a = 1;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
-- ok - "c" is not in PK but it is part of REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_pk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
-- fail - "a" is not in REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_nopk SET a = 1;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (c > 99);
-- ok - "c" is part of REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_nopk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
-- Tests for partitioned table
@@ -404,6 +412,7 @@ ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1);
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (b > 99);
-- fail - "b" is not in REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_part_pk SET a = 1;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub6';
DROP PUBLICATION testpub6;
DROP TABLE rf_tbl_abcd_pk;
base-commit: a6a4641252ed166ba187d7fbe0504ddb5a5f0e33
--
2.34.1
