---
title: "A backfill with session_replication_role = replica skips foreign keys | Proofline"
description: "Setting session_replication_role = replica is a common way to keep a backfill from firing the table's triggers."
url: https://proofline.sh/learn/postgres-session-replication-role-foreign-keys/
---

[Learning center](/learn/)

# A backfill with session\_replication\_role = replica skips foreign keys

Setting session\_replication\_role = replica is a common way to keep a backfill from firing the table's triggers. It also turns off foreign key checks for that session, because PostgreSQL enforces foreign keys with triggers. In our lab, a copy into a new table with a foreign key succeeded with two orphaned rows, and the constraint still reported itself as validated afterwards.

Breaking migrations The Proofline team November 22, 2026 2 min read

## 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`](https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-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](/learn/postgres-backfill-side-effect-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](/learn/postgres-foreign-key-existing-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](/learn/data-migration-across-service-versions/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Keep a backfill from firing the table's update triggers](/learn/postgres-backfill-side-effect-triggers/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Adding a PostgreSQL foreign key to a table with orphaned rows](/learn/postgres-foreign-key-existing-rows/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
