Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sTMK0-00FQAV-2U for pgsql-general@arkaria.postgresql.org; Mon, 15 Jul 2024 14:04:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sTMJy-00AQKG-MM for pgsql-general@arkaria.postgresql.org; Mon, 15 Jul 2024 14:04:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sTMJy-00AQK8-7D for pgsql-general@lists.postgresql.org; Mon, 15 Jul 2024 14:04:54 +0000 Received: from mail-oa1-x2b.google.com ([2001:4860:4864:20::2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sTMJv-002AnL-BF for pgsql-general@postgresql.org; Mon, 15 Jul 2024 14:04:52 +0000 Received: by mail-oa1-x2b.google.com with SMTP id 586e51a60fabf-25e3b8637c9so1734714fac.3 for ; Mon, 15 Jul 2024 07:04:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721052290; x=1721657090; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=AlaWrWy6EDxy6dzUPgjvgxsQEEhjPagw37U3a8BMxo8=; b=S9rVsWY8x5NV5QI3dRt39m2g0TmUqbQWORsaP4oPKr0tRe3soSxk310PpbE3YafT1q SQKCUJ0wa6h4LlUOVacYn2DeIyOvH0YtZw80x4UC5fv4dtLqoUYgp97VXggr2X5AhbmA A3G/cSY3CqeGPUIXhd6nrNK8lziwyvRBJ0CLBVEsQE/LRULrgxqezwDYpxRNVdBcz+Lj N5QpBH3RHPw3Jd7LyaTnx9pdfsXo+j5W4BBooZwIJPpSBNFswx74voS/LfBwWw+eIuCP MQeSJjly/uUP0V9C++Wd3V5mm8Yi+2Wz3sikBih1HKoO1J5yAkBCyzFnRy/sn9pSLfiC 57aw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721052290; x=1721657090; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=AlaWrWy6EDxy6dzUPgjvgxsQEEhjPagw37U3a8BMxo8=; b=ONIlph3j6XfLPORrMWy051Us8S0SqjtwZc9IBGHSk9/sB/grhEguGH8Zq3VTRTtKcK CCZdWWmrpquWXbS/MtN3br4bbhrc8CxA81XF2vMeUPzo8ukaMjWPOirFufBA5qLruxQV tafBQCVc3EQjsxJt1Sp0FRSrgo2ByG1K9KT7LxW2ieBOj9Vcl4I9FlKVqltJJnZaA81N ZTInEd+/rICBL/rcsxzmGwAVpOqLivSGv3tbb3F1gGowpb3kg55rCELNuHx+9a2F0DNK Masr1OaNZYvA4j4vYVrO7aXcYKS1q2iHJWDz+G/O1bKOC2d9sGvqQjrNXHLXzv28GN1R 1b+g== X-Gm-Message-State: AOJu0YxUrG1WtdczXBrqxljyroy30sB6wssd0kNQHazo0QhnkEDYBe32 IfFVAu0MTtAKrHB9Kst9LzDVCaCSnMocdF16o6ZeX++0Qd7hR4Iq2enxTTej+U/k72gWr7yBjLr 0QYwpxmWeOEOB8L331O//92J4boznR8W1 X-Google-Smtp-Source: AGHT+IFbK9LBZlnjgDIAF7QcXQ/nrT/uJUGsuUIo/2geqhqKs/SdxOkY3AbRFphHfh6TNxz6tS2g5H69U4pr9fZfKYA= X-Received: by 2002:a05:6870:169c:b0:24c:afec:f1c with SMTP id 586e51a60fabf-25eaec6d8admr13488722fac.48.1721052290232; Mon, 15 Jul 2024 07:04:50 -0700 (PDT) MIME-Version: 1.0 From: Ron Johnson Date: Mon, 15 Jul 2024 10:04:39 -0400 Message-ID: Subject: How does this FK constraint error happen? To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000663847061d49b60f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000663847061d49b60f Content-Type: text/plain; charset="UTF-8" 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) --000000000000663847061d49b60f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
PG 14.12

The job does=C2=A0DELETE FROM rel_group_user;=C2=A0(no WHER= E clause!!) then does=C2=A0DELETE FRO= M public.access_user;=C2=A0(also no WHERE clause), but the=C2=A0public.access_user=C2=A0statement = fails on FK constraint=C2=A0error on=C2=A0rel_group_user=C2=A0(which was just recently emptied).

Each statement is in a different transaction, since they a= re executed via separate psql statements.=C2=A0 Thus, no apparent MVCC visi= bility weirdness.

My first thought, of course= , was that there are=C2=A0two=C2=A0rel_grou= p_user=C2=A0tables.=C2=A0 Alas, no, there's just one.=C2=A0 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 r= el_group_user;
DELETE 42747
[snip]
2024-07-15 02:41:15 Deleting fr= om FISPTAPPGS401DA/TAPd.public.access_user
DELETE FROM public.access_use= r;
ERROR: =C2=A0update or delete on table "access_user" violat= es foreign key constraint "fk_rel_group_user_1" on table "re= l_group_user"
DETAIL: =C2=A0Key (user_id)=3D(1210) is still referen= ced from table "rel_group_user".
ERROR: deleting FISPTAPPGS401= DA/TAPd.public.access_user
[snip]

$ psql --host=3DFISPTAPPGS401DA TAPd -X= c "\dt *.*" | grep " rel_group_user "
<= div>=C2=A0public =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | rel_group_user =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| table = =C2=A0 =C2=A0 =C2=A0 | TAP

Here are the= =C2=A0table definitions (if relevant):
T= APd=3D# \d public.access_user
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table &qu= ot;public.access_user"
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Col= umn =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Collation | Nullabl= e | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Default = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
--= --------------------------+-----------------------------+-----------+------= ----+----------------------------------------------
=C2=A0user_id =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| integer = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | nextval('access_user_user_= id_seq'::regclass)
=C2=A0login_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | character varying(255) =C2=A0 =C2=A0 =C2=A0|= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
[snip]
=C2=A0Indexe= s:
=C2=A0 =C2=A0 "pk_access_user" PRIMARY KEY, btree (user_id)=
=C2=A0 =C2=A0 "idx_user_login_id" UNIQUE, btree (login_id)Foreign-key constraints:
=C2=A0 =C2=A0 "fk_access_user_home_domain= " FOREIGN KEY (home_domain_id) REFERENCES access_domain(domain_id)
= =C2=A0 =C2=A0 "fk_user_userdesktop" FOREIGN KEY (user_desktop_id)= REFERENCES user_desktop(user_desktop_id)
Referenced by:
[snip]
= =C2=A0 =C2=A0 TABLE "rel_group_user" CONSTRAINT "fk_rel_grou= p_user_1" FOREIGN KEY (user_id) REFERENCES access_user(user_id)
[sn= ip]

TAPd=3D# \d rel_group_user
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.rel_gro= up_user"
=C2=A0 =C2=A0Column =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Collat= ion | Nullable | Default
-------------+-----------------------------+--= ---------+----------+---------
=C2=A0user_id =C2=A0 =C2=A0 | integer =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
=C2=A0group_id =C2=A0 =C2=A0|= integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
=C2=A0modified_by= | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0|
=C2=A0modified_on | timestamp without time zone | =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 | not null |
Indexes:
=C2=A0 =C2=A0 "idx_= rel_group_user" UNIQUE, btree (user_id, group_id)
=C2=A0 =C2=A0 &qu= ot;idx_rel_group_user_groupid" btree (group_id)
=C2=A0 =C2=A0 "= ;idx_rel_group_user_userid" btree (user_id)
Foreign-key constraints= :
=C2=A0 =C2=A0 "fk_rel_group_user_1" FOREIGN KEY (user_id) RE= FERENCES access_user(user_id)
=C2=A0 =C2=A0 "fk_rel_group_user_2&qu= ot; FOREIGN KEY (group_id) REFERENCES access_group(group_id)



--000000000000663847061d49b60f--