Re: [PATCH] Disallow column lists on tables with REPLICA IDENTITY FULL when publishing updates/deletes

Hi,

On Sun, Oct 25, 2026 at 4:41 AM Mohit Kumar <mohit(dot)bash(at)gmail(dot)com> wrote:
> I would like to propose a patch to catch an invalid publication configuration
> at DDL time rather than deferring the failure to runtime DML.
>
> Problem:
> Currently, CREATE PUBLICATION and ALTER PUBLICATION allow specifying a column
> list on tables that have REPLICA IDENTITY FULL, even when the publication publishes
> UPDATE and DELETE operations (the default).
>
> For example:
>
> CREATE TABLE mytab (id int, val text, extra text);
> ALTER TABLE mytab REPLICA IDENTITY FULL;
>
> -- 1. This succeeds silently with no warning or error:
> CREATE PUBLICATION mypub FOR TABLE mytab (id, val);
>
> -- 2. Inserts work fine:
> INSERT INTO mytab VALUES (1, 'hello', 'world');
>
> -- 3. Any subsequent UPDATE or DELETE fails in production:
> UPDATE mytab SET val = 'updated' WHERE id = 1;
> -- ERROR: cannot update table "mytab"
> -- DETAIL: Column list used by the publication does not cover the replica identity.
>
> This recently caused an unexpected production outage for us: the schema migration
> succeeded cleanly without error in CI/CD, but application web traffic immediately
> began failing on subsequent UPDATE and DELETE operations.
>
> Proposed Solution:
> Because REPLICA IDENTITY FULL requires the entire row to identify tuples on the
> subscriber, a column list on such a table can never work for updates or deletes.

Thank you for the report and for sharing the patch.

Could you take a look at a related proposal thread from earlier[1]? It explains
why this check wasn't included initially, and it also highlights some corner
cases that might need attention if similar checks were added.

I'm not opposed to adding checks in the DDL path, but I suspect the proposal
here may run into the same considerations as the earlier one. It might be worth
confirming that once, so these cases can be handled correctly from the start.
And if the patch decides to leave some corner cases aside, a brief note in the
comments explaining why would be very helpful for future readers.

[1] https://www.postgresql.org/message-id/flat/CAKz%3D%3Db%2B-%2BQFiqtpzDYXYfCUjoKJUN9VjpdfM-Fou44C9KaQCZg%40mail.gmail.com#35881adb054b6f2de1b97ee1a179a497

Best Regards,
Zhijie Hou