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: shveta malik <shveta.malik@gmail.com>
Date: Wed, 8 Apr 2026 15:52:14 +0530
Message-ID: 
 <CAJpy0uAHiwssGCgE54zHgDPPhV4h+O5aeM03D-go8EjY1dNPCg@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>,
 Amit Kapila <amit.kapila16@gmail.com>,
	Dilip Kumar <dilipbalaut@gmail.com>,
	Postgres hackers <pgsql-hackers@lists.postgresql.org>,
 shveta malik <shveta.malik@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAJpy0uAHiwssGCgE54zHgDPPhV4h%2BO5aeM03D-go8EjY1dNPCg%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 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.
>
> 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.


Even though I like the idea of keeping replica identity as a table
property, this raises a question about the need for a new FORCE
option. With this approach, the user must explicitly opt REPLICA
IDENTITY FORCE for tables without a primary key (pt 4 in your design).
But if the user is already expected to take an explicit action, they
could simply set REPLICA IDENTITY FULL themselves at table creation
time or via ALTER TABLE. From what I understood, the original
requirement was to ensure that tables without a primary key do not
silently fail replication, and that this is handled automatically
without manual intervention. In contrast, the FORCE approach still
requires users to explicitly configure replica identity for each
table, which seems to reintroduce the same operational burden.

For example, instead of:
ALTER TABLE t1 REPLICA IDENTITY FORCE;
the user could simply do:
ALTER TABLE t1 REPLICA IDENTITY FULL

By the time, the user is explicitly configuring replica identity, they
would already need to understand the replication semantics, so it's
not clear what additional benefit FORCE provides over FULL. Am I
missing something here?

thanks
Shveta