Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Thu, 18 Dec 2025 11:49:02 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "GRANT ZHOU" <grantzhou@gmail.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>
Message-Id: <19dac243-1f46-4720-bdec-bf0f851d03b9@app.fastmail.com>
In-Reply-To: 
 <CA+FXcm-YZXJpc6E7XEDTv9Yaic=U7Dwnjj4znxJ4gCxUZMcXww@mail.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>
Subject: Re: Improve logical replication usability when tables lack primary
 keys
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/19dac243-1f46-4720-bdec-bf0f851d03b9%40app.fastmail.com>
Precedence: bulk

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=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.
>

As I tried to explain in the previous email, the problem with FOR ALL TA=
BLES
and FOR TABLES IN SCHEMA syntax is that the is no catalog information ab=
out the
relations; the list of relations is collected at runtime.

When I suggested "use a script" I was referring to fix the logical repli=
cation
setup regarding the lack of primary key. There is no need to have an aut=
omation
outside the database, use an event trigger. If your lazy user doesn't cr=
eate
the primary key, assign REPLICA IDENTITY FULL. Something like

-- This example is far from being a complete solution for fixing the lac=
k 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_tes=
t_3;

8<----------------------------------------------------------------------=
------8<

> 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
>

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=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.
>

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.

I think you are pursuing the wrong solution. IMO we need a solution to e=
nforce
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 co=
mplies
with the publication properties (update and delete properties should req=
uire an
appropriate replica identity). We should close the gaps in both publicat=
ion and
table.


--=20
Euler Taveira
EDB   https://www.enterprisedb.com/