The copy#
The old shipments table has no foreign key, and two of its 10,000 rows point at customer 4242, who does not exist. The new shipments_v2 table adds the constraint. A sync trigger on shipments keeps the copy current for rows the running release writes. We ran this on PostgreSQL 16.15:
CREATE TABLE shipments_v2 (
id bigint PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers,
carrier_code text
);
INSERT INTO shipments_v2 SELECT id, customer_id, upper(carrier) FROM shipments
ON CONFLICT DO NOTHING;
ERROR: insert or update on table "shipments_v2" violates foreign key constraint "shipments_v2_customer_id_fkey"
DETAIL: Key (customer_id)=(4242) is not present in table "customers".
That error is the useful outcome: it names the bad data before the new table holds it. The same copy with the replication role set:
BEGIN;
SET LOCAL session_replication_role = replica;
INSERT INTO shipments_v2 SELECT id, customer_id, upper(carrier) FROM shipments
ON CONFLICT DO NOTHING;
COMMIT;
-- INSERT 0 10000
All 10,000 rows went in, including both orphans.
Why validation does not notice#
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'shipments_v2'::regclass AND contype = 'f';
-- shipments_v2_customer_id_fkey | t
ALTER TABLE shipments_v2 VALIDATE CONSTRAINT shipments_v2_customer_id_fkey;
-- succeeds, and checks nothing
The constraint was created valid on an empty table. VALIDATE CONSTRAINT only checks constraints marked NOT VALID, so it has nothing to do. The documentation for session_replication_role warns about exactly this: with replica, triggers that are not marked ENABLE REPLICA or ENABLE ALWAYS do not fire, foreign key triggers included, and it can leave data inconsistent. Only an explicit query finds the orphans:
SELECT s.id, s.customer_id FROM shipments_v2 s
LEFT JOIN customers c ON c.id = s.customer_id
WHERE c.id IS NULL;
| id | customer_id |
|---|---|
| 17 | 4242 |
| 18 | 4242 |
A reader that joins shipments_v2 to customers with an inner join now loses these shipments without an error.
What to use instead#
Keep triggers on and make each one decide whether to act, with a transaction-scoped setting, as keep a backfill from firing the table's update triggers does. The foreign key keeps checking every row the backfill writes.
If you need the replication role, for example for a bulk load where the trigger cost matters, add the constraint as NOT VALID before the load and run VALIDATE CONSTRAINT after it, which scans the table. Add a foreign key to a table with orphaned rows covers what that scan locks. Setting the parameter needs superuser rights or a GRANT SET ON PARAMETER, so the backfill's role may not be able to use it at all.
The check#
After any backfill that ran with the replication role, run the anti-join for every foreign key on the target table. Pass when each returns no row. The full sequence is in moving data while two application releases share one database.