BUG #18516: Foreign key data integrity is not validated when reenabled the trigger on tables - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18516
Logged by:          Muzammil Q
Email address:      mujjamil995@gmail.com
PostgreSQL version: 15.6
Operating system:   rehl7
Description:

Hi,
I come up with a weird issue in the PostgreSQL 15.6 version. To update the
data in the tables I generally disable all triggers on the table using the
superuser privilege and enable the after the data copy to avoid the checking
the parent and child tables sequence. During the enable process FK
constraint should check the data integrity between the child and parent
tables. but somehow this is skipped and trigger enabled without any issue
and later during the data restoration this issue is identified.

Please check the below steps and share your feedback on this.

To recreate the issue you can follow the below steps: -

a)Create parent and child tables
test_db=# create table database_login (id int generated always as
identity,dbname varchar, login varchar);
test_db=# create table database ( id int generated always as identity,
server_name varchar,dbname varchar primary key);
CREATE TABLE

b)Add FK constraint 
test_db=# alter table database_login add constraint dbname_fk FOREIGN
KEY(dbname) REFERENCES database(dbname);
ALTER TABLE
test_db=# \d database_login
                          Table "public.database_login"
 Column |       Type        | Collation | Nullable |           Default
--------+-------------------+-----------+----------+------------------------------
 id     | integer           |           | not null | generated always as
identity
 dbname | character varying |           |          |
 login  | character varying |           |          |
Foreign-key constraints:
    "dbname_fk" FOREIGN KEY (dbname) REFERENCES database(dbname)

test_db=# \d database
                                Table "public.database"
   Column    |       Type        | Collation | Nullable |
Default
-------------+-------------------+-----------+----------+------------------------------
 id          | integer           |           | not null | generated always
as identity
 server_name | character varying |           |          |
 dbname      | character varying |           | not null |
Indexes:
    "database_pkey" PRIMARY KEY, btree (dbname)
Referenced by:
    TABLE "database_login" CONSTRAINT "dbname_fk" FOREIGN KEY (dbname)
REFERENCES database(dbname)

c)Insert the test data into the tables

test_db=# insert into database(server_name,dbname)
values('test_server1','test_db_1');
INSERT 0 1
test_db=# select * from database;
 id | server_name  |  dbname
----+--------------+-----------
  1 | test_server1 | test_db_1
(1 row)

test_db=# insert into database_login(dbname,login)
values('test_db_1','abc_user');
INSERT 0 1

d) The Below error shows when the triggers are enabled 
test_db=# insert into database_login(dbname,login)
values('test_db_2','abc_user');
ERROR:  insert or update on table "database_login" violates foreign key
constraint "dbname_fk"
DETAIL:  Key (dbname)=(test_db_2) is not present in table "database".

e)Disable the trigger and update the data

test_db=# alter table database_login disable trigger all;
ALTER TABLE
test_db=# alter table database disable trigger all;
ALTER TABLE
test_db=# insert into database_login(dbname,login)
values('test_db_2','abc_user');
INSERT 0 1

f)Enable the trigger. At this point PostgreSQL should check the data
integrity between the tables. But this is not checked and PG is allowed to
enable the trigger and you can see the output of the database_login table in
which test_db_2 db entry is available and this is not present in the parent
database table. When you restore the dump to another schema then it will
give the fk violate error.

test_db=# alter table database  enable  trigger all;
ALTER TABLE
test_db=# alter table database_login enable  trigger all;
ALTER TABLE
test_db=# select * from database_login;
 id |  dbname   |  login
----+-----------+----------
  1 | test_db_1 | abc_user
  3 | test_db_2 | abc_user
(2 rows)

test_db=# select * from database;
 id | server_name  |  dbname
----+--------------+-----------
  1 | test_server1 | test_db_1
(1 row)

test_db=# \d database
                                Table "public.database"
   Column    |       Type        | Collation | Nullable |
Default
-------------+-------------------+-----------+----------+------------------------------
 id          | integer           |           | not null | generated always
as identity
 server_name | character varying |           |          |
 dbname      | character varying |           | not null |
Indexes:
    "database_pkey" PRIMARY KEY, btree (dbname)
Referenced by:
    TABLE "database_login" CONSTRAINT "dbname_fk" FOREIGN KEY (dbname)
REFERENCES database(dbname)

test_db=# \d database_login
                          Table "public.database_login"
 Column |       Type        | Collation | Nullable |           Default
--------+-------------------+-----------+----------+------------------------------
 id     | integer           |           | not null | generated always as
identity
 dbname | character varying |           |          |
 login  | character varying |           |          |
Foreign-key constraints:
    "dbname_fk" FOREIGN KEY (dbname) REFERENCES database(dbname)