Warn on missing replica identity in CREATE/ALTER PUBLICATION

On Tue, Apr 21, 2026 at 11:06 AM 南拓弥 <minamitakuya@lifull.com> wrote:

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.

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?

/messages/by-id/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com

thanks
Shveta

# 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]/messages/by-id/a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com 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]: /messages/by-id/a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com

Best regards,

2026年4月22日(水) 12:33 shveta malik <shveta.malik@gmail.com>:

On Tue, Apr 21, 2026 at 11:06 AM 南拓弥 <minamitakuya@lifull.com> wrote:

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.

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?

/messages/by-id/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com

thanks
Shveta

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

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

南 拓弥 minamitakuya@lifull.com

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

On Apr 23, 2026, at 13:46, 南拓弥 <minamitakuya@lifull.com> wrote:

# 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] /messages/by-id/a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com

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/

On Thu, Apr 23, 2026 at 12:21 PM Chao Li <li.evan.chao@gmail.com> wrote:

On Apr 23, 2026, at 13:46, 南拓弥 <minamitakuya@lifull.com> wrote:

# 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] /messages/by-id/a9da608f-24be-4213-a712-8592852d37f1@app.fastmail.com

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.

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