Postgresql allows nonexistent foreign keys

Question

I have a rails app that uses postgresql 12. Recently, I wrote some tests and saw some strange behavior. I have a countries table. Its schema looks like that:

qq2_test=# \d countries;
                                               Table "public.countries"
        Column         |              Type              | Collation | Nullable |                Default                
-----------------------+--------------------------------+-----------+----------+---------------------------------------
 id                    | bigint                         |           | not null | nextval('countries_id_seq'::regclass)
 domain                | character varying              |           | not null | ''::character varying
 root_city_id          | bigint                         |           |          | 
 language_id           | bigint                         |           | not null | 
 currency_id           | bigint                         |           | not null | 
 google_tag_manager_id | character varying              |           | not null | ''::character varying
 created_at            | timestamp(6) without time zone |           | not null | 
 updated_at            | timestamp(6) without time zone |           | not null | 
Indexes:
    "countries_pkey" PRIMARY KEY, btree (id)
    "index_countries_on_domain" UNIQUE, btree (domain)
    "index_countries_on_currency_id" btree (currency_id)
    "index_countries_on_language_id" btree (language_id)
Foreign-key constraints:
    "fk_rails_6f479b409c" FOREIGN KEY (language_id) REFERENCES languages(id)
    "fk_rails_7cac1212c7" FOREIGN KEY (currency_id) REFERENCES currencies(id)
    "fk_rails_beac36a0bd" FOREIGN KEY (root_city_id) REFERENCES cities(id)
Referenced by:
    TABLE "country_translations" CONSTRAINT "fk_rails_0c4ee35f26" FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
    TABLE "countries_languages" CONSTRAINT "fk_rails_556e7398aa" FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
    TABLE "cities" CONSTRAINT "fk_rails_996e05be41" FOREIGN KEY (country_id) REFERENCES countries(id)

As you can see, I have foreign key constraints on both currency_id and language_id fields. When I run my tests I see that there are records in that table:

qq2_test=# select * from countries;
    id     | domain | root_city_id | language_id | currency_id | google_tag_manager_id |         created_at         |         updated_at         
-----------+--------+--------------+-------------+-------------+-----------------------+----------------------------+----------------------------
 665664142 | com    |              |  1019186233 |   432072940 |                       | 2020-10-23 06:20:49.288637 | 2020-10-23 06:20:49.288637
 169150333 | by     |              |  1019186233 |   432072940 |                       | 2020-10-23 06:20:49.288637 | 2020-10-23 06:20:49.288637
(2 rows)

There are two my test records and they have language and currency references. But their tables are empty:

qq2_test=# select * from currencies;
 id | name | symbol | created_at | updated_at 
----+------+--------+------------+------------
(0 rows)

qq2_test=# select * from languages;
 id | name | locale | image | created_at | updated_at 
----+------+--------+-------+------------+------------
(0 rows)

Why does postgresql allow nonexistent references in countries table?

Ruby 2.7.1 MRI
Rails: 6.0.3.4
Postgresql 12.4
Ubuntu 20.04

score 2 · Answer 1 · answered Oct 23 '20 at 07:11

2

There are only two options:

The foreign key constraint is NOT VALID.

Such constraints are cheched for new entries, but existing entries can violate them.

But that would show up in your \d output, so that is not the case.
You have data corruption.

Apart from hardware problems or software bugs, possible explanations are:
- Someone set session_replication_role = replica so that triggers don't fire.
- A superuser ran
```
ALTER TABLE countries DISABLE TRIGGER ALL;
```

answered Oct 23 '20 at 07:11

Laurenz Albe

209,280
17
206
263

Thanks so much for help. Indeed, rails tests do series of 'disable trigger - insert - enable trigger' queries. Also I found explanation in rails docs https://guides.rubyonrails.org/testing.html#fixtures-in-action – airled Oct 23 '20 at 08:51

richyen · Accepted Answer · 2020-10-23T07:30:39.990

Assuming that you're the only person using the database (since you are talking about small, 1-2 row tests), I would guess that your Rails app (or corresponding driver) is disabling triggers or foreign key checks. It's totally possible to bypass the foreign key checks like so:

edb=# show session_replication_role ;
 session_replication_role 
--------------------------
 origin
(1 row)

edb=# create table city (id int primary key, name text);
CREATE TABLE
edb=# select * from city;
 id | name 
----+------
(0 rows)

edb=# create table person (id int, name text, city int references city(id));
CREATE TABLE
edb=# insert into person values (1,'foo',1);
ERROR:  insert or update on table "person" violates foreign key constraint "person_city_fkey"
DETAIL:  Key (city)=(1) is not present in table "city".
edb=# set session_replication_role to replica;
SET
edb=# insert into person values (1,'foo',1);
INSERT 0 1
edb=# select * from person;
 id | name | city 
----+------+------
  1 | foo  |    1
(1 row)

edb=# select * from city;
 id | name 
----+------
(0 rows)

I would suggest that you temporarily set log_statement = all and run your tests again--then see in your Postgres server logs (default should be /var/log/postgresql/postgresql-12-main.log for Ubuntu) what might be disabling your foreign key constraint checks, then address your findings accordingly.

Thanks for help! Yes, it disables all triggers on all tables. https://guides.rubyonrails.org/testing.html#fixtures-in-action — airled, Oct 23 '20 at 08:52

Postgresql allows nonexistent foreign keys

2 Answers2