Thread

1. Warn on missing replica identity in CREATE/ALTER PUBLICATION

   南拓弥 <minamitakuya@lifull.com> — 2026-04-21T05:35:35Z

   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,
   
   
   
   

2. Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION

   shveta malik <shveta.malik@gmail.com> — 2026-04-22T03:33:06Z

   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
   
   
   
   

3. Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION

   南拓弥 <minamitakuya@lifull.com> — 2026-04-23T05:46:53Z

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

4. Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION

   Chao Li <li.evan.chao@gmail.com> — 2026-04-23T06:50:22Z

   
   > 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] https://www.postgresql.org/message-id/a9da608f-24be-4213-a712-8592852d37f1%40app.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/
   
   
   
   
   
   
   
   

5. Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION

   shveta malik <shveta.malik@gmail.com> — 2026-04-23T07:07:41Z

   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] https://www.postgresql.org/message-id/a9da608f-24be-4213-a712-8592852d37f1%40app.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