Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION - Mailing list pgsql-hackers

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

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?
>
> https://www.postgresql.org/message-id/flat/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp%2Bz0bbK57VZ%2BOkJTgJQVQ%40mail.gmail.com
>
> thanks
> Shveta



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

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

南 拓弥 minamitakuya@lifull.com

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