MIME-Version: 1.0
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>
 <a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com>
In-Reply-To: <a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com>
From: GRANT ZHOU <grantzhou@gmail.com>
Date: Wed, 17 Dec 2025 13:43:31 -0800
Message-ID: 
 <CA+FXcm-YZXJpc6E7XEDTv9Yaic=U7Dwnjj4znxJ4gCxUZMcXww@mail.gmail.com>
Subject: Re: Improve logical replication usability when tables lack primary
 keys
To: Euler Taveira <euler@eulerto.com>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>,
 Chao Li <li.evan.chao@gmail.com>,
	Amit Kapila <amit.kapila16@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>,
	Postgres hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000644ee706462cbeae"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BFXcm-YZXJpc6E7XEDTv9Yaic%3DU7Dwnjj4znxJ4gCxUZMcXww%40mail.gmail.com>
Precedence: bulk

--000000000000644ee706462cbeae
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Dec 17, 2025 at 12:50=E2=80=AFPM Euler Taveira <euler@eulerto.com> =
wrote:

> 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.
>
Thanks for the detailed history. I completely understand and respect that
Replica Identity is designed as a table property.

> Each table needs to say what's its row identifier. The user created a tab=
le
> without primary key. Well, create a primary key. There are dozens of
> thousands
> of objects. Use a script.

However, I=E2=80=99d like to share a user perspective regarding the "use a =
script"
approach. The main value of FOR TABLES IN SCHEMA is *in-database automation=
*.
If users still need to maintain external scripts to monitor and ALTER new
tables to prevent replication errors, it significantly diminishes the value
of that automation.

Additionally, tables without Primary Keys are valid SQL and extremely
common in enterprise environments (e.g., audit logs, data warehousing). In
large-scale deployments, enforcing PKs on every single table isn't always
practical.

> 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 reliabl=
e
> 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].


I think the goal of this proposal is not to change the underlying table
property design, but rather to seek a mechanism (like a Publication option)
to ensure this automation functions safely without external intervention.
It is simply about allowing the database to handle these valid, common
scenarios gracefully when automation is enabled.

--
Grant Zhou
Highgo Software

--000000000000644ee706462cbeae
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Dec 17, 2025 at 12:50=E2=80=AFPM =
Euler Taveira &lt;<a href=3D"mailto:euler@eulerto.com">euler@eulerto.com</a=
>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex">The ship has sailed a long time a=
go (version 9.4 to be precise -- commit<br>
07cacba983ef). The row identifier property was defined as an SQL command (A=
LTER<br>
TABLE ... REPLICA IDENTITY) and *not* a publication property. IMO that&#39;=
s the<br>
correct design because row identifier is a table property. Extend this conc=
ept<br>
to a publication property is the wrong direction. It is confusing and compl=
ex.<br></blockquote><p>Thanks for the detailed history. I completely unders=
tand and respect that Replica Identity is designed as a table property.</p>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex">
Each table needs to say what&#39;s its row identifier. The user created a t=
able<br>
without primary key. Well, create a primary key. There are dozens of thousa=
nds<br>
of objects. Use a script.</blockquote><div><p>However, I=E2=80=99d like to =
share a user perspective regarding the &quot;use a script&quot; approach.
The main value of <code>FOR TABLES IN SCHEMA</code> is <b>in-database autom=
ation</b>. If users still need to maintain external scripts to monitor and =
<code>ALTER</code> new tables to prevent replication errors, it significant=
ly diminishes the value of that automation.</p><p>Additionally, tables with=
out Primary Keys are valid SQL and extremely common in enterprise environme=
nts (e.g., audit logs, data warehousing). In large-scale deployments, enfor=
cing PKs on every single table isn&#39;t always practical.=C2=A0</p></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex">I would suggest a way to d=
isallow or add a warning<br>
message while creating the publication or adding new tables, however, the F=
OR<br>
ALL TABLES and FOR TABLES IN SCHEMA were mentioned. There isn&#39;t a relia=
ble way<br>
to guarantee that a publication with UPDATE and/or DELETE option contains o=
nly<br>
tables with pk, RI FULL or RI USING INDEX. The fact that there is no rows i=
n the<br>
pg_publication_rel for these clauses, makes validating the CREATE/ALTER<br>
PUBLICATION commands more difficult. (I prefer deterministic commands and w=
hen I<br>
saw an object definition saying &quot;including objects created in the futu=
re&quot;, my<br>
first question is: what&#39;s the drawbacks and caveats?)<br>
<br>
I don&#39;t think the current behavior is lacking documentation; the REPLIC=
A<br>
IDENTITY concept is explicitly in the logical replication chapter [1].</blo=
ckquote><div><br></div><div>I think the goal of this proposal is not to cha=
nge the underlying table property design, but rather to seek a mechanism (l=
ike a Publication option) to ensure this automation functions safely withou=
t external intervention. It is simply about allowing the database to handle=
 these valid, common scenarios gracefully when automation is enabled.</div>=
<div><br></div><div>--</div><div>Grant Zhou</div><div>Highgo Software</div>=
</div></div>

--000000000000644ee706462cbeae--