Обсуждение: Warn on missing replica identity in CREATE/ALTER PUBLICATION
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,
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
# 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 ━━━━━━━━━━━━━━━━━━━━
> 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 twofeatures 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] 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 thetwo 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