Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: Improve logical replication usability when tables lack primary
 keys
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: <19dac243-1f46-4720-bdec-bf0f851d03b9@app.fastmail.com>
Date: Fri, 19 Dec 2025 16:08:39 +0800
Cc: GRANT ZHOU <grantzhou@gmail.com>,
 "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>,
 Amit Kapila <amit.kapila16@gmail.com>,
 Dilip Kumar <dilipbalaut@gmail.com>,
 Postgres hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <875BBCC0-CF08-4136-8E9E-F03DF75C3A11@gmail.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>
 <a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com>
 <CA+FXcm-YZXJpc6E7XEDTv9Yaic=U7Dwnjj4znxJ4gCxUZMcXww@mail.gmail.com>
 <19dac243-1f46-4720-bdec-bf0f851d03b9@app.fastmail.com>
To: Euler Taveira <euler@eulerto.com>
Archived-At: 
 <https://www.postgresql.org/message-id/875BBCC0-CF08-4136-8E9E-F03DF75C3A11%40gmail.com>
Precedence: bulk


> On Dec 18, 2025, at 22:49, Euler Taveira <euler@eulerto.com> wrote:
>=20
> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>> On Wed, Dec 17, 2025 at 12:50=E2=80=AFPM Euler Taveira =
<euler@eulerto.com> wrote:
>>> 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.
>> However, I=E2=80=99d like to share a user perspective regarding the =
"use a=20
>> script" approach. The main value of `FOR TABLES IN SCHEMA` is=20
>> *in-database automation*. If users still need to maintain external=20
>> scripts to monitor and `ALTER` new tables to prevent replication=20
>> errors, it significantly diminishes the value of that automation.
>>=20
>=20
> As I tried to explain in the previous email, the problem with FOR ALL =
TABLES
> and FOR TABLES IN SCHEMA syntax is that the is no catalog information =
about the
> relations; the list of relations is collected at runtime.
>=20
> When I suggested "use a script" I was referring to fix the logical =
replication
> setup regarding the lack of primary key. There is no need to have an =
automation
> outside the database, use an event trigger. If your lazy user doesn't =
create
> the primary key, assign REPLICA IDENTITY FULL. Something like
>=20
> -- This example is far from being a complete solution for fixing the =
lack of
> -- primary key in a logical replication scenario.
> -- ALTER TABLE should be supported too
> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
>        RETURNS event_trigger LANGUAGE plpgsql AS $$
> DECLARE
>    obj record;
>    rec record;
>    ricnt integer :=3D 0;
> BEGIN
>    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
>    LOOP
>        IF obj.command_tag =3D 'CREATE TABLE' THEN
>            SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid =3D =
obj.objid AND indisprimary;
>            RAISE NOTICE 'ricnt: %', ricnt;
>            IF ricnt =3D 0 THEN
>                EXECUTE 'ALTER TABLE ' || obj.object_identity || ' =
REPLICA IDENTITY FULL';
>            END IF;
>        END IF;
>    END LOOP;
> END;
> $$;
>=20
> CREATE EVENT TRIGGER event_trigger_for_replica_identity
>    ON ddl_command_end
>    EXECUTE FUNCTION event_trigger_for_replica_identity();
>=20
> CREATE TABLE event_trigger_test_1 (a int);
> \d+ event_trigger_test_1
> CREATE TABLE event_trigger_test_2 (a int primary key);
> \d+ event_trigger_test_2
> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary =
key(b));
> \d+ event_trigger_test_3
> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT =
event_trigger_test_3_pkey;
> --\d+ event_trigger_test_3
>=20
> DROP EVENT TRIGGER event_trigger_for_replica_identity;
> DROP FUNCTION event_trigger_for_replica_identity;
> DROP TABLE event_trigger_test_1, event_trigger_test_2, =
event_trigger_test_3;
>=20
> =
8<------------------------------------------------------------------------=
----8<
>=20
>> Additionally, tables without Primary Keys are valid SQL and extremely=20=

>> common in enterprise environments (e.g., audit logs, data =
warehousing).=20
>> In large-scale deployments, enforcing PKs on every single table isn't=20=

>> always practical.=20
>>=20
>=20
> I'm not saying users shouldn't create tables without a primary key. =
I'm arguing
> that this decision should take into account what adjustments need to =
be made to
> use these tables in logical replication.
>=20
>>=20
>> I think the goal of this proposal is not to change the underlying =
table=20
>> property design, but rather to seek a mechanism (like a Publication=20=

>> option) to ensure this automation functions safely without external=20=

>> intervention. It is simply about allowing the database to handle =
these=20
>> valid, common scenarios gracefully when automation is enabled.
>>=20
>=20
> You didn't get it. You already have one property to handle it and you =
are
> proposing to add a second property to handle it.
>=20
> I think you are pursuing the wrong solution. IMO we need a solution to =
enforce
> that the logical replication contract is valid. If you create or =
modify a table
> that is part of a publication, there is no validation that that table =
complies
> with the publication properties (update and delete properties should =
require an
> appropriate replica identity). We should close the gaps in both =
publication and
> table.
>=20

If I summarize Euler=E2=80=99s position in short words: discipline over =
convenience. I actually strongly agree with that. In PG we generally =
prefer explicit over implicit behavior, and predictability over magic.

Based on the discussion so far, I think we share the following design =
goals:

1) Keep replica identity as a table property.
2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in =
tables without primary keys.
3) Avoid global or implicit behavior changes.
4) Preserve explicit opt-in for higher WAL cost.
5) Keep the logical replication contract explicit and enforceable.

I=E2=80=99ve been thinking about whether adding a new replica identity =
could meet these goals.

Today we have four replica identities: DEFAULT (PK, fallback to NONE), =
INDEX, FULL, and NONE.

What if we introduce a new replica identity, tentatively called =
=E2=80=9CFORCE=E2=80=9D: PK with fallback to FULL. (Let=E2=80=99s keep =
our focus on the design, not argue the name for now.)

With this approach:

1) Replica identity remains a table property.
2) Publication membership is still evaluated at runtime, so FOR TABLES =
IN SCHEMA is not special-cased.
3) No new GUCs are required.
4) The user must explicitly opt in by setting the replica identity. Once =
FORCE is chosen, adding or dropping a primary key later does not =
silently break UPDATE/DELETE replication.
5) The logical replication contract remains explicit; the table declares =
that it is safe for UPDATE/DELETE replication even without a PK, at the =
cost of higher WAL volume.

This feels like a small, explicit extension of the existing RI =
semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional =
behavior (PK fallback
to NONE), so conditional RI behavior is not new, this would just make a =
different fallback explicit and user-chosen.

After that, we could consider a database-level default_replica_identity =
setting, applied at table creation time, for environments that want this =
behavior consistently. But that would only make sense if we first agree =
on the table-level mechanism.

I=E2=80=99m interested in whether this direction aligns better with the =
goals above.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/