public inbox for [email protected]
help / color / mirror / Atom feedWarn on missing replica identity in CREATE/ALTER PUBLICATION
5+ messages / 3 participants
[nested] [flat]
* Warn on missing replica identity in CREATE/ALTER PUBLICATION
@ 2026-04-21 05:35 南拓弥 <[email protected]>
2026-04-22 03:33 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION shveta malik <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: 南拓弥 @ 2026-04-21 05:35 UTC (permalink / raw)
To: [email protected]
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,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION
2026-04-21 05:35 Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
@ 2026-04-22 03:33 ` shveta malik <[email protected]>
2026-04-23 05:46 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: shveta malik @ 2026-04-22 03:33 UTC (permalink / raw)
To: 南拓弥 <[email protected]>; +Cc: [email protected]; shveta malik <[email protected]>
On Tue, Apr 21, 2026 at 11:06 AM 南拓弥 <[email protected]> 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%4...
thanks
Shveta
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION
2026-04-21 05:35 Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
2026-04-22 03:33 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION shveta malik <[email protected]>
@ 2026-04-23 05:46 ` 南拓弥 <[email protected]>
2026-04-23 06:50 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION Chao Li <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: 南拓弥 @ 2026-04-23 05:46 UTC (permalink / raw)
To: shveta malik <[email protected]>; +Cc: [email protected]
# 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 <[email protected]>:
>
> On Tue, Apr 21, 2026 at 11:06 AM 南拓弥 <[email protected]> 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%4...
>
> thanks
> Shveta
--
━━━━━━━━━━━━━━━━━━━━
あらゆるLIFEを、FULLに。
株式会社LIFULL
テクノロジー本部 事業基盤U
プラットフォームG
南 拓弥 [email protected]
〒102-0083 東京都千代田区麹町1-4-4
━━━━━━━━━━━━━━━━━━━━
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION
2026-04-21 05:35 Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
2026-04-22 03:33 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION shveta malik <[email protected]>
2026-04-23 05:46 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
@ 2026-04-23 06:50 ` Chao Li <[email protected]>
2026-04-23 07:07 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION shveta malik <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Chao Li @ 2026-04-23 06:50 UTC (permalink / raw)
To: 南拓弥 <[email protected]>; +Cc: shveta malik <[email protected]>; [email protected]
> On Apr 23, 2026, at 13:46, 南拓弥 <[email protected]> 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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION
2026-04-21 05:35 Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
2026-04-22 03:33 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION shveta malik <[email protected]>
2026-04-23 05:46 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
2026-04-23 06:50 ` Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION Chao Li <[email protected]>
@ 2026-04-23 07:07 ` shveta malik <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: shveta malik @ 2026-04-23 07:07 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: 南拓弥 <[email protected]>; [email protected]; shveta malik <[email protected]>
On Thu, Apr 23, 2026 at 12:21 PM Chao Li <[email protected]> wrote:
>
>
>
> > On Apr 23, 2026, at 13:46, 南拓弥 <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-04-23 07:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-21 05:35 Warn on missing replica identity in CREATE/ALTER PUBLICATION 南拓弥 <[email protected]>
2026-04-22 03:33 ` shveta malik <[email protected]>
2026-04-23 05:46 ` 南拓弥 <[email protected]>
2026-04-23 06:50 ` Chao Li <[email protected]>
2026-04-23 07:07 ` shveta malik <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox