How does this FK constraint error happen? - Mailing list pgsql-general
From | Ron Johnson |
---|---|
Subject | How does this FK constraint error happen? |
Date | |
Msg-id | CANzqJaBF6mnUy1ZfBbySzSr1AfErnrMEGJaUvdSA1H9ZXkTbEw@mail.gmail.com Whole thread Raw |
Responses |
Re: How does this FK constraint error happen?
|
List | pgsql-general |
PG 14.12
The job does DELETE FROM rel_group_user; (no WHERE clause!!) then does DELETE FROM public.access_user; (also no WHERE clause), but the public.access_user statement fails on FK constraint error on rel_group_user (which was just recently emptied).
Each statement is in a different transaction, since they are executed via separate psql statements. Thus, no apparent MVCC visibility weirdness.
My first thought, of course, was that there are two rel_group_user tables. Alas, no, there's just one. See below for grep statement.
Excerpts from the cron job log file:
[snip]
2024-07-15 02:40:04 Deleting from FISPTAPPGS401DA/TAPd.rel_group_user
DELETE FROM rel_group_user;
DELETE 42747
[snip]
2024-07-15 02:41:15 Deleting from FISPTAPPGS401DA/TAPd.public.access_user
DELETE FROM public.access_user;
ERROR: update or delete on table "access_user" violates foreign key constraint "fk_rel_group_user_1" on table "rel_group_user"
DETAIL: Key (user_id)=(1210) is still referenced from table "rel_group_user".
ERROR: deleting FISPTAPPGS401DA/TAPd.public.access_user
[snip]
2024-07-15 02:40:04 Deleting from FISPTAPPGS401DA/TAPd.rel_group_user
DELETE FROM rel_group_user;
DELETE 42747
[snip]
2024-07-15 02:41:15 Deleting from FISPTAPPGS401DA/TAPd.public.access_user
DELETE FROM public.access_user;
ERROR: update or delete on table "access_user" violates foreign key constraint "fk_rel_group_user_1" on table "rel_group_user"
DETAIL: Key (user_id)=(1210) is still referenced from table "rel_group_user".
ERROR: deleting FISPTAPPGS401DA/TAPd.public.access_user
[snip]
$ psql --host=FISPTAPPGS401DA TAPd -Xc "\dt *.*" | grep " rel_group_user "
public | rel_group_user | table | TAP
Here are the table definitions (if relevant):
TAPd=# \d public.access_user
Table "public.access_user"
Column | Type | Collation | Nullable | Default
----------------------------+-----------------------------+-----------+----------+----------------------------------------------
user_id | integer | | not null | nextval('access_user_user_id_seq'::regclass)
login_id | character varying(255) | | not null |
[snip]
Indexes:
"pk_access_user" PRIMARY KEY, btree (user_id)
"idx_user_login_id" UNIQUE, btree (login_id)
Foreign-key constraints:
"fk_access_user_home_domain" FOREIGN KEY (home_domain_id) REFERENCES access_domain(domain_id)
"fk_user_userdesktop" FOREIGN KEY (user_desktop_id) REFERENCES user_desktop(user_desktop_id)
Referenced by:
[snip]
TABLE "rel_group_user" CONSTRAINT "fk_rel_group_user_1" FOREIGN KEY (user_id) REFERENCES access_user(user_id)
[snip]
TAPd=# \d rel_group_user
Table "public.rel_group_user"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
user_id | integer | | not null |
group_id | integer | | not null |
modified_by | integer | | |
modified_on | timestamp without time zone | | not null |
Indexes:
"idx_rel_group_user" UNIQUE, btree (user_id, group_id)
"idx_rel_group_user_groupid" btree (group_id)
"idx_rel_group_user_userid" btree (user_id)
Foreign-key constraints:
"fk_rel_group_user_1" FOREIGN KEY (user_id) REFERENCES access_user(user_id)
"fk_rel_group_user_2" FOREIGN KEY (group_id) REFERENCES access_group(group_id)
Table "public.access_user"
Column | Type | Collation | Nullable | Default
----------------------------+-----------------------------+-----------+----------+----------------------------------------------
user_id | integer | | not null | nextval('access_user_user_id_seq'::regclass)
login_id | character varying(255) | | not null |
[snip]
Indexes:
"pk_access_user" PRIMARY KEY, btree (user_id)
"idx_user_login_id" UNIQUE, btree (login_id)
Foreign-key constraints:
"fk_access_user_home_domain" FOREIGN KEY (home_domain_id) REFERENCES access_domain(domain_id)
"fk_user_userdesktop" FOREIGN KEY (user_desktop_id) REFERENCES user_desktop(user_desktop_id)
Referenced by:
[snip]
TABLE "rel_group_user" CONSTRAINT "fk_rel_group_user_1" FOREIGN KEY (user_id) REFERENCES access_user(user_id)
[snip]
TAPd=# \d rel_group_user
Table "public.rel_group_user"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
user_id | integer | | not null |
group_id | integer | | not null |
modified_by | integer | | |
modified_on | timestamp without time zone | | not null |
Indexes:
"idx_rel_group_user" UNIQUE, btree (user_id, group_id)
"idx_rel_group_user_groupid" btree (group_id)
"idx_rel_group_user_userid" btree (user_id)
Foreign-key constraints:
"fk_rel_group_user_1" FOREIGN KEY (user_id) REFERENCES access_user(user_id)
"fk_rel_group_user_2" FOREIGN KEY (group_id) REFERENCES access_group(group_id)
pgsql-general by date: