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>
 <CA+FXcm-YZXJpc6E7XEDTv9Yaic=U7Dwnjj4znxJ4gCxUZMcXww@mail.gmail.com>
 <19dac243-1f46-4720-bdec-bf0f851d03b9@app.fastmail.com>
 <875BBCC0-CF08-4136-8E9E-F03DF75C3A11@gmail.com>
In-Reply-To: <875BBCC0-CF08-4136-8E9E-F03DF75C3A11@gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Mon, 22 Dec 2025 17:18:41 +0530
Message-ID: 
 <CAA4eK1KMOTYGSQdoPBZsJ6T-QEw5eYHG0padQXTViu7LeeFWMw@mail.gmail.com>
Subject: Re: Improve logical replication usability when tables lack primary
 keys
To: Chao Li <li.evan.chao@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>, GRANT ZHOU <grantzhou@gmail.com>,
	"houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>,
 Dilip Kumar <dilipbalaut@gmail.com>,
	Postgres hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1KMOTYGSQdoPBZsJ6T-QEw5eYHG0padQXTViu7LeeFWMw%40mail.gmail.com>
Precedence: bulk

On Fri, Dec 19, 2025 at 1:39=E2=80=AFPM Chao Li <li.evan.chao@gmail.com> wr=
ote:
>
> > On Dec 18, 2025, at 22:49, Euler Taveira <euler@eulerto.com> wrote:
> >
> > 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 "u=
se 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.
> >>
> >
> > As I tried to explain in the previous email, the problem with FOR ALL T=
ABLES
> > and FOR TABLES IN SCHEMA syntax is that the is no catalog information a=
bout the
> > relations; the list of relations is collected at runtime.
> >
> > When I suggested "use a script" I was referring to fix the logical repl=
ication
> > setup regarding the lack of primary key. There is no need to have an au=
tomation
> > outside the database, use an event trigger. If your lazy user doesn't c=
reate
> > the primary key, assign REPLICA IDENTITY FULL. Something like
> >
> > -- This example is far from being a complete solution for fixing the la=
ck 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 || ' REPLI=
CA IDENTITY FULL';
> >            END IF;
> >        END IF;
> >    END LOOP;
> > END;
> > $$;
> >
> > CREATE EVENT TRIGGER event_trigger_for_replica_identity
> >    ON ddl_command_end
> >    EXECUTE FUNCTION event_trigger_for_replica_identity();
> >
> > 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
> >
> > 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_te=
st_3;
> >
> > 8<---------------------------------------------------------------------=
-------8<
> >
> >> 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'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.
> >
> >>
> >> I think the goal of this proposal is not to change the underlying tabl=
e
> >> 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.
> >>
> >
> > You didn't get it. You already have one property to handle it and you a=
re
> > proposing to add a second property to handle it.
> >
> > 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 c=
omplies
> > with the publication properties (update and delete properties should re=
quire an
> > appropriate replica identity). We should close the gaps in both publica=
tion and
> > table.
> >

If we want, we can ensure that any table added to that specific
publication (that has an option replica_identy=3D'full') would
automatically override the default to FULL, if PK is not available.
This information can be cached to avoid overhead.

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

You haven't told why we can't consider a custom event trigger as
suggested by Euler for customers who are not willing to change the RI
default explicitly for each table. I think it is worth considering
providing a custom solution outside core-postgres for your customers
for this specific case.

> Based on the discussion so far, I think we share the following design goa=
ls:
>
> 1) Keep replica identity as a table property.
> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in table=
s 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 co=
uld meet these goals.
>
> Today we have four replica identities: DEFAULT (PK, fallback to NONE), IN=
DEX, FULL, and NONE.
>
> What if we introduce a new replica identity, tentatively called =E2=80=9C=
FORCE=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 b=
reak 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 cos=
t 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 fal=
lback
> to NONE), so conditional RI behavior is not new, this would just make a d=
ifferent fallback explicit and user-chosen.
>
> After that, we could consider a database-level default_replica_identity s=
etting, applied at table creation time, for environments that want this beh=
avior consistently. But that would only make sense if we first agree on the=
 table-level mechanism.
>

I don't much like the database-level option as it expects a new
default to be introduced. I think the internal working will almost be
same as the option at publication-level.

--=20
With Regards,
Amit Kapila.