From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mujjamil995@gmail.com
Subject: BUG #18516: Foreign key data integrity is not validated when reenabled the trigger on tables
Date: Thu, 20 Jun 2024 09:10:16 +0000
Message-ID: <18516-a7e2ae00e4ccf5ca@postgresql.org> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mujjamil995@gmail.com
Subject: Re: BUG #18516: Foreign key data integrity is not validated when reenabled the trigger on tables
In-Reply-To: <18516-a7e2ae00e4ccf5ca@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox