Re: Improve logical replication usability when tables lack primary keys

> On Dec 22, 2025, at 19:48, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Dec 19, 2025 at 1:39 PM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
>>
>>> On Dec 18, 2025, at 22:49, Euler Taveira <euler(at)eulerto(dot)com> wrote:
>>>
>>> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>>>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <euler(at)eulerto(dot)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’d 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.
>>>>
>>>
>>> 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.
>>>
>>> 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
>>>
>>> -- 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 := 0;
>>> BEGIN
>>> FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
>>> LOOP
>>> IF obj.command_tag = 'CREATE TABLE' THEN
>>> SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
>>> RAISE NOTICE 'ricnt: %', ricnt;
>>> IF ricnt = 0 THEN
>>> EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA 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_test_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 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.
>>>>
>>>
>>> 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 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.
>>>
>
> If we want, we can ensure that any table added to that specific
> publication (that has an option replica_identy='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’s 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.
>>
>
> 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.

Thanks for raising this. Let me clarify why we don’t consider a custom event trigger a satisfactory solution in practice, even though it is technically possible.

I discussed this with our field teams, and some customers have indeed experimented with event-trigger-based solutions before. However, they generally don’t prefer them for this use case.

First, the required logic is non-trivial and fragile. The trigger would need to track table creation, primary key creation and removal, and distinguish between cases where REPLICA IDENTITY FULL was set implicitly versus explicitly by the user. Handling all these cases correctly makes the solution feel like a workaround rather than a robust enforcement mechanism.

Second, event triggers introduce operational risk. They need to be installed, monitored, and maintained separately from the core system. If a trigger is accidentally dropped, disabled, or modified, the behavior silently changes, which is particularly risky for replication semantics.

Third, customers place much higher trust in core PostgreSQL behavior than in custom scripts layered on top. Issues caused by core behavior are seen as something that can be understood, worked around, or fixed by upgrading, whereas failures caused by custom triggers are harder to diagnose and are often attributed to the overall solution quality.

For these reasons, while event triggers can work as a stopgap, our customers strongly prefer a solution where the replication contract is enforced by core PostgreSQL rather than external mechanisms.

>
>> 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’ve 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 “FORCE”: PK with fallback to FULL. (Let’s 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 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.

That’s fair. I agree that a database-level option wouldn’t be fundamentally different from a publication-level solution and would likely share most of the same internal mechanics.

At this point nothing is decided yet; we’re still exploring different approaches and trying to understand the trade-offs.

I have a question to better understand how a publication-level approach would behave in edge cases.

Since replica identity is defined on tables and a table can belong to multiple publications, how should UPDATE/DELETE be handled if the same table is added to two publications with different expectations?

For example, suppose a table without a PK is added to:
- pub_a, which does not require FULL (or effectively falls back to NONE)
- pub_b, which requires FULL for UPDATE/DELETE

In this case, should UPDATE/DELETE on the table be allowed at all, and if so, based on which publication’s semantics? What do you think?

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