Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Wed, 17 Dec 2025 17:49:03 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>,
 "Chao Li" <li.evan.chao@gmail.com>, "Amit Kapila" <amit.kapila16@gmail.com>
Cc: "Dilip Kumar" <dilipbalaut@gmail.com>,
 "Postgres hackers" <pgsql-hackers@lists.postgresql.org>
Message-Id: <a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com>
In-Reply-To: 
 <TY4PR01MB16907E1C68EA6EBADCE54ABBE94ABA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
References: 
 <CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com>
 <CAA4eK1+UL6wVDNzkpHjA7RVLD_8AkrP2tu+RvQ2h5AUjyEe+-Q@mail.gmail.com>
 <5ABD7727-CD22-4112-A186-0E788EE78109@gmail.com>
 <CAFiTN-ucvk8JOiLvjii6VXar6nYJvCQDgzp8_4v55yweUmzdzw@mail.gmail.com>
 <23A24BFF-18A7-4FE9-AAFA-13E1AA207DD0@gmail.com>
 <CAA4eK1KzjxO-qWjWSox6e6AWH4FVU5ZPEgeZ+na=eyov7umutg@mail.gmail.com>
 <D4A3A4D7-2F51-4F0D-9FDE-95E9C318148E@gmail.com>
 <TY4PR01MB16907E1C68EA6EBADCE54ABBE94ABA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
Subject: Re: Improve logical replication usability when tables lack primary
 keys
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/a9da608f-24be-4213-a712-8592852d37f1%40app.fastmail.com>
Precedence: bulk

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/