MIME-Version: 1.0
References: 
 <CAP6NMUKYgRJ3Z5ThhtXpYYkRBffT=fAStwQ3_1Cab0tREZ=5NQ@mail.gmail.com>
 <CAJpy0uAd2BY_jinMWscfLr11WHra7sxrNc18jKbkhp=4CkAFig@mail.gmail.com>
In-Reply-To: 
 <CAJpy0uAd2BY_jinMWscfLr11WHra7sxrNc18jKbkhp=4CkAFig@mail.gmail.com>
From: =?UTF-8?B?5Y2X5ouT5byl?= <minamitakuya@lifull.com>
Date: Thu, 23 Apr 2026 14:46:53 +0900
Message-ID: 
 <CAP6NMUJEvkD1BKm8YCVch5YhcwqaVeLQXWGQhqF-+gdDufgs3w@mail.gmail.com>
Subject: Re: Warn on missing replica identity in CREATE/ALTER PUBLICATION
To: shveta malik <shveta.malik@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAP6NMUJEvkD1BKm8YCVch5YhcwqaVeLQXWGQhqF-%2BgdDufgs3w%40mail.gmail.com>
Precedence: bulk

# 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 =E2=80=94 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-8592852d3=
7f1%40app.fastmail.com

Best regards,

2026=E5=B9=B44=E6=9C=8822=E6=97=A5(=E6=B0=B4) 12:33 shveta malik <shveta.ma=
lik@gmail.com>:
>
> On Tue, Apr 21, 2026 at 11:06=E2=80=AFAM =E5=8D=97=E6=8B=93=E5=BC=A5 <min=
amitakuya@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 =E2=80=94 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 TAB=
LES
> > - ALTER PUBLICATION ... ADD/SET TABLE / ADD/SET TABLES IN SCHEMA
> > - ALTER PUBLICATION ... SET (publish =3D '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=E2=80=99d like to highlight that
> there=E2=80=99s an ongoing thread addressing a similar issue. The propose=
d
> 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/CAEoWx2mMorbMwjKbT4YCsjDyL3r9M=
p%2Bz0bbK57VZ%2BOkJTgJQVQ%40mail.gmail.com
>
> thanks
> Shveta


--=20
=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=
=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=
=81=E2=94=81=E2=94=81=E2=94=81
=E3=81=82=E3=82=89=E3=82=86=E3=82=8BLIFE=E3=82=92=E3=80=81FULL=E3=81=AB=E3=
=80=82

=E6=A0=AA=E5=BC=8F=E4=BC=9A=E7=A4=BELIFULL
=E3=83=86=E3=82=AF=E3=83=8E=E3=83=AD=E3=82=B8=E3=83=BC=E6=9C=AC=E9=83=A8 =
=E4=BA=8B=E6=A5=AD=E5=9F=BA=E7=9B=A4U
=E3=83=97=E3=83=A9=E3=83=83=E3=83=88=E3=83=95=E3=82=A9=E3=83=BC=E3=83=A0G

=E5=8D=97 =E6=8B=93=E5=BC=A5 minamitakuya@lifull.com

=E3=80=92102-0083 =E6=9D=B1=E4=BA=AC=E9=83=BD=E5=8D=83=E4=BB=A3=E7=94=B0=E5=
=8C=BA=E9=BA=B9=E7=94=BA1-4-4
=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=
=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=81=E2=94=
=81=E2=94=81=E2=94=81=E2=94=81