Subject: Warn on missing replica identity in CREATE/ALTER PUBLICATION

From: 南拓弥 <[email-masked]>
Date: Tue, 21 Apr 2026 14:35:35 +0900

Hi hackers,

CREATE PUBLICATION silently succeeds even when target tables lack a
usable replica identity, while the publication publishes UPDATE and/or
DELETE. The error only surfaces later at replication time:

  ERROR: cannot delete from table "foo" because it does not have a
  replica identity and publishes deletes

This gap has caused real production incidents — in one case, a CDC
pipeline using FOR TABLES IN SCHEMA included a table without a primary
key, and replication stalled for hours before the cause was found.

I'd like to propose emitting a WARNING at publication creation/alter
time when this mismatch exists. The check would cover all paths:

- CREATE PUBLICATION ... FOR TABLE / FOR TABLES IN SCHEMA / FOR ALL TABLES
- ALTER PUBLICATION ... ADD/SET TABLE / ADD/SET TABLES IN SCHEMA
- ALTER PUBLICATION ... SET (publish = 'update, delete')

The approach I'm considering is a publication-level check that runs
after the final publication state is known, scanning the effective set
of published tables via GetIncludedPublicationRelations() /
GetAllSchemaPublicationRelations() / GetAllPublicationRelations() and
checking each table's replica identity.

I have a working prototype for the FOR TABLE / ADD TABLE paths. A few
open questions before I post a full patch:

1. For FOR ALL TABLES, the check would scan pg_class. Acceptable for
   a DDL operation, or too expensive?

2. Should we cap the number of warnings when many tables are affected?

3. Should this be controllable via a GUC, or is a simple WARNING
   sufficient?

Thoughts welcome.

--
Best regards,

---

From: shveta malik <[email-masked]>
Date: Wed, 22 Apr 2026 09:03:06 +0530

> 3. Should this be controllable via a GUC, or is a simple WARNING

Before we dive deeper into this idea, I’d like to highlight that
there’s an ongoing thread addressing a similar issue. The proposed
approach there is to implement a fallback RI in such scenarios to
prevent replication-time errors caused by missing RI. Could you please
review this ([1]) and confirm whether it meets your requirements?

https://www.postgresql.org/message-id/flat/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp%2Bz0bbK57VZ%2BOkJTgJQVQ%40mail.gmail.com

thanks
Shveta

---

From: 南拓弥 <[email-masked]>
Date: Thu, 23 Apr 2026 14:46:53 +0900

# Reply draft v2 to Shveta

---

Hi Shveta,

Thanks for pointing out that thread. I've read through it carefully.

I believe the two proposals address different aspects of the same
problem:

- The fallback RI approach changes runtime behavior so that tables
  without a primary key can still replicate UPDATE/DELETE.
- This proposal simply warns at DDL time that a publication contains
  tables whose replica identity will cause UPDATE/DELETE to fail at
  replication time.

A WARNING at publication creation time is useful regardless of whether
a fallback mechanism exists, because:

- If a table has REPLICA IDENTITY DEFAULT with no primary key, it
  silently falls back to NOTHING. Combining that with a publication
  that publishes updates/deletes is guaranteed to fail at runtime.
  A WARNING at DDL time closes this gap.
- Even users who explicitly set REPLICA IDENTITY NOTHING and add the
  table to an update/delete publication would benefit from a reminder,
  since that combination cannot succeed.
- The WARNING does not change any existing behavior — it only makes
  the misconfiguration visible earlier.

Notably, Euler mentioned in that thread [1] that he would "suggest a
way to disallow or add a warning message while creating the
publication or adding new tables", which is exactly what this proposal
does.

That said, I see the two proposals as complementary. Should I continue
this as a separate thread, or would it be better to join the existing
discussion?

I have a working patch covering all publication paths (FOR TABLE,
FOR TABLES IN SCHEMA, FOR ALL TABLES, ALTER PUBLICATION). Happy to
post it either way.

[1] https://www.postgresql.org/message-id/a9da608f-24be-4213-a712-8592852d37f1%40app.fastmail.com

Best regards,

-- 
━━━━━━━━━━━━━━━━━━━━
あらゆるLIFEを、FULLに。

株式会社LIFULL
テクノロジー本部 事業基盤U
プラットフォームG

南 拓弥 [email-masked]

〒102-0083 東京都千代田区麹町1-4-4
━━━━━━━━━━━━━━━━━━━━

---

From: Chao Li <[email-masked]>
Date: Thu, 23 Apr 2026 14:50:22 +0800

> On Apr 23, 2026, at 13:46, 南拓弥 <[email-masked]> wrote:
> 

You are very welcome to join the thread, as the initiator of that thread.

I am not personally against your idea of adding such a warning message, but I think it would be better to consider the two features together as a whole solution from a system perspective.

In any case, new features will have to wait for v20 until July, so we still have time for more discussion and deeper consideration.

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

---

From: shveta malik <[email-masked]>
Date: Thu, 23 Apr 2026 12:37:41 +0530

> In any case, new features will have to wait for v20 until July, so we still have time for more discussion and deeper consideration.

I agree. But if the RI fallback option gets delayed due to lack of
consensus on the design or other reasons, issuing a WARNING during
publication creation seems like a reasonable approach.

thanks
Shveta