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]
$ 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)
