Postgres 11 not enforcing foreign key constraint?

Our PostgreSQL 11 database has tables A and B.

postgres=# \d A;
                              Table "public.A"
       Column        |            Type             | Collation | Nullable | Default 
---------------------+-----------------------------+-----------+----------+---------
 id                  | bigint                      |           | not null | 
 b_id                | bigint                      |           | not null | 
 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
    "idx_a_bid" btree (b_id)
Foreign-key constraints:
    "fk_a_bid" FOREIGN KEY (b_id) REFERENCES A(id)

postgres=# select count(*) from A where b_id = 522039;
 count 
-------
    90
(1 row)
                  
postgres=# select count(*) from B where id = 522039;
 count 
-------
     0
(1 row)

There seems to be an unenforced FK from A.b_id to B.id.

Triggers were never disabled on these tables (so in theory FK checks have been applied).

What is an explanation for this other than someone disabling triggers in the past and removing rows from B?

Solution

What could make PostgreSQL violate a foreign key constraint? There are three possible answers, and each has to do with the fact that foreign keys are implemented by system triggers on the referenced and the referencing tables. These triggers are not shown in the output of \dt or in information_schema.triggers, and you have to query the catalog pg_trigger to see them.

Violate a foreign key by setting `session_replication_role`

If session_replication_role = replica, normal triggers don't fire. Only triggers that have been changed with on of these statements will fire:

ALTER TABLE ... ENABLE REPLICA TRIGGER ...
ALTER TABLE ... ENABLE ALWAYS TRIGGER ...

It takes a superuser to change session_replication_role, and it takes a superuser to modify the system triggers that implement foreign keys.

Violate a foreign key constraint by disabling triggers on a table

This can be done with

ALTER TABLE ... DISABLE TRIGGER ALL

on the referencing or referenced table. This statement can only be executed by a superuser. Depending on whether you disabled triggers on the referenced or the referencing table, certain aspects of foreign key constraints will no longer be checked.

Violate a cascading foreign key with a user-defined trigger

Create a constraint with

ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id) ON DELETE CASCADE;

Then create a trigger like this:

CREATE FUNCTION cancel_delete() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   RETURN NULL;
END;$$;

CREATE TRIGGER cancel_delete BEFORE DELETE ON a
   FOR EACH ROW EXECUTE FUNCTION cancel_delete();

Now if you delete a row in b, a system trigger will delete rows in a, but the trigger you created will abort these deletes without an error, since it returns NULL. That will break referential integrity.

Exotic ways to create broken foreign keys

you can create a NOT VALID foreign key constraint (but that would show up in \d)
you can experience data corruption that silently loses data in one of the tables
you can directly mess with the catalogs, for example by inserting a row in pg_constraint

So what caused the foreign key to be broken?

The first two methods require a superuser to do something wrong, but the third method is open to normal users. If you can exclude that a superuser has messed with the database, it has to be the third method.