[PATCH] Disallow column lists on tables with REPLICA IDENTITY FULL when publishing updates/deletes
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254373psql -h localhost -U postgresBuilt from patchset v1 (message #1), October 24, 2026 at 08:52 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254373_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254373_1 && git checkout t254373_1Patchset v1 (message #1) is on t254373_1
Hi hackers,
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.
The attached patch adds early validation in
CheckPubRelationColumnList() and
AlterPublicationOptions():
1. During CREATE PUBLICATION and ALTER PUBLICATION ... ADD/SET TABLE,
if the
publication publishes UPDATE or DELETE and the table has REPLICA
IDENTITY
FULL, an immediate error is raised:
"ERROR: cannot use column list for relation "..." in publication
"...""
"DETAIL: Column lists cannot be specified for tables with REPLICA
IDENTITY FULL
when publishing updates or deletes."
"HINT: Either change the table's REPLICA IDENTITY or configure the
publication
to publish only 'insert'."
2. During ALTER PUBLICATION ... SET (publish = ...), if a publication
already
contains a table with a column list and REPLICA IDENTITY FULL,
adding 'update'
or 'delete' to publish actions is rejected.
3. Insert-only publications (e.g. publish = 'insert') remain allowed and
unaffected.
Regression tests in publication.sql and publication.out have been
updated,
and all regression tests pass.
Looking forward to your feedback.
Thanks,
Mohit