How does this FK constraint error happen?

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

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)