Hi,
On Mon, Jun 30, 2025 at 3:14 AM Ashutosh Bapat <[email protected]>
wrote:
>
> 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.
This seems like a reasonable, simple, and useful addition, so +1.
> 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. But the output seems to be useful.
> E.g. from the tests
pg_publication_tables is a view emitting one row per table if it's part of
a publication, and replica identity being a table-level property, it should
be fine to extend for convenience.
> 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)
>
> This line gives all the information related to logical replication of
> table rf_tbl_abcd_part_pk together.
After getting the replica identity as 'default', 'using index', 'full', or
'nothing', I think the next important thing to look at is what exactly is
being used in the 'using index' and 'default' cases. Can we show the exact
index name and/or OID as well - if not as separate columns, then as
something like 'using index idx_a' or 'default (idx_b)'? For debugging
issues like replication lag, heavy WAL generation, or slow apply, what
matters most is telling exactly which index is being used for this replica
identity.
Some comments on the patch:
1/ + when 'i' then 'index'
Can we be consistent with the keywords and use 'using index'?
2/ In the tests, I see multiple test cases for each category spread across.
Can we have one simple test case per just one category (e.g., one for
'using index') to reduce the diff?
> I researched this a bit more and then I found some inconsistency
> between the code and the documentation
>
> At https://www.postgresql.org/docs/current/sql-createpublication.html,
> we mention that changes to a partition table will use identity of a
> partititioned table when publish_via_partition_root is true.
>
> Which identity are we talking about here? If that's replica identity,
> the documentation is wrong since code says otherwise
> In pub_rf_contains_invalid_column()
>
> /*
> * For a partition, if pubviaroot is true, find the topmost ancestor that
> * is published via this publication as we need to use its row filter
> * expression to filter the partition's changes.
> *
> * Note that even though the row filter used is for an ancestor, the
> * REPLICA IDENTITY used will be for the actual child table.
> */
Yes, it looks like the docs are wrong for replica identity unless I'm
missing something. I tried the following test case:
Test setup:
CREATE TABLE pt (a int, b int, c int) PARTITION BY RANGE (a);
CREATE TABLE pt_p1 PARTITION OF pt FOR VALUES FROM (0) TO (100);
CREATE TABLE pt_p2 PARTITION OF pt FOR VALUES FROM (100) TO (200);
ALTER TABLE pt REPLICA IDENTITY FULL;
CREATE PUBLICATION p_root FOR TABLE pt WITH (publish_via_partition_root =
true);
INSERT INTO pt VALUES (1, 10, 100);
UPDATE pt SET b = 999 WHERE a = 1;
DELETE FROM pt WHERE a = 1;
(leaf pt_p1/pt_p2 stay REPLICA IDENTITY DEFAULT with no PK = NOTHING; root
pt is FULL)
Expectation per the docs is that changes use the identity of the root, root
is FULL, so UPDATE/DELETE should succeed.
But UPDATE/DELETE errors out. It looks like the publisher uses the leaf
partition's replica identity, not the root's:
ERROR: cannot update table "pt_p1" because it does not have a replica
identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
ERROR: cannot delete from table "pt_p1" because it does not have a replica
identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER
TABLE.
> Let me revise possible solutions so that this view can report replica
> identity even in case of publish_via_partition_root
>
> 1. If a publication has publish_via_partition_root true, then for all
> the partitioned tables we show replica identity of the root partition
> suffixed by (mixed). E.g. full (mixed). Add a note in documentation
> explaining the meaning of suffix "mixed".
>
> 2. If a publication has publish_via_partition_root true, then for all
> the partitioned tables we show a set of distinct replica identities of
> its partitions and also the replica identity of the root. If all the
> partitions have the same replica identity setting, only one replica
> identity type will be reported. Otherwise, the user will be able to
> know all the possible replica identity settings that may be used. Add
> a note to the documentation explaining the meaning of this value for a
> partitioned table.
>
> 3. Let the view report all the partitions as well as the partition
> root. Against each relation, report its replica identity. Expand the
> pg_publication_tables view to also report the relation via which the
> changes are published (under column name "publish_via"). When
> publish_via_partition_root is true, publish_via reports the top
> partitioned relation. I think that gives full information about tables
> being published through the publication.
>
> What do you think?
It looks more reasonable and useful to emit both the root table and all its
partitions.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
