Re: Improve logical replication usability when tables lack primary keys

On Wed, Dec 17, 2025, at 8:09 AM, Zhijie Hou (Fujitsu) wrote:
>
> And I also prefer using a publication option as it's always beneficial to
> minimize unnecessary WAL generation whenever possible.
>

The ship has sailed a long time ago (version 9.4 to be precise -- commit
07cacba983ef). The row identifier property was defined as an SQL command (ALTER
TABLE ... REPLICA IDENTITY) and *not* a publication property. IMO that's the
correct design because row identifier is a table property. Extend this concept
to a publication property is the wrong direction. It is confusing and complex.

Each table needs to say what's its row identifier. The user created a table
without primary key. Well, create a primary key. There are dozens of thousands
of objects. Use a script. I would suggest a way to disallow or add a warning
message while creating the publication or adding new tables, however, the FOR
ALL TABLES and FOR TABLES IN SCHEMA were mentioned. There isn't a reliable way
to guarantee that a publication with UPDATE and/or DELETE option contains only
tables with pk, RI FULL or RI USING INDEX. The fact that there is no rows in the
pg_publication_rel for these clauses, makes validating the CREATE/ALTER
PUBLICATION commands more difficult. (I prefer deterministic commands and when I
saw an object definition saying "including objects created in the future", my
first question is: what's the drawbacks and caveats?)

I don't think the current behavior is lacking documentation; the REPLICA
IDENTITY concept is explicitly in the logical replication chapter [1].

[1] https://www.postgresql.org/docs/current/logical-replication-publication.html

--
Euler Taveira
EDB https://www.enterprisedb.com/