Date: Mon, 15 Jul 2024 16:35:18 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: How does this FK constraint error happen?
Message-ID: <20240715143518.3v5xdfuj27ryzuzh@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CANzqJaBF6mnUy1ZfBbySzSr1AfErnrMEGJaUvdSA1H9ZXkTbEw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="aq5g2imjziqhhxno"
Content-Disposition: inline
In-Reply-To: <CANzqJaBF6mnUy1ZfBbySzSr1AfErnrMEGJaUvdSA1H9ZXkTbEw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240715143518.3v5xdfuj27ryzuzh%40hjp.at>
Precedence: bulk


--aq5g2imjziqhhxno
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-07-15 10:04:39 -0400, Ron Johnson wrote:
> The job does=A0DELETE FROM rel_group_user;=A0(no WHERE clause!!) then doe=
s=A0DELETE
> FROM public.access_user;=A0(also no WHERE clause), but the=A0public.acces=
s_user
> =A0statement fails on FK constraint=A0error on=A0rel_group_user=A0(which =
was just
> recently emptied).
>=20
> Each statement is in a different transaction, since they are executed via
> separate psql statements.=A0 Thus, no apparent MVCC visibility weirdness.
>=20
> My first thought, of course, was that there are=A0two=A0rel_group_user=A0=
tables.=A0
> Alas, no, there's just one.=A0 See below for grep statement.
>=20
> 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: =A0update or delete on table "access_user" violates foreign key co=
nstraint
> "fk_rel_group_user_1" on table "rel_group_user"
> DETAIL: =A0Key (user_id)=3D(1210) is still referenced from table "rel_gro=
up_user".
> ERROR: deleting FISPTAPPGS401DA/TAPd.public.access_user
> [snip]

Is it possible that some other process created an entry in
rel_group_user between these two queries?

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--aq5g2imjziqhhxno
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmaVM6YACgkQ8g5IURL+
KF38HQ//XfjUwHuGOIc93vXDTbrdr79wIzH/z1Z1P9ft6713SvI2ctnbKrzsK4ya
IasgFfRm44Ek/iZO5CrZ6OQO9Nlnk3GXanBph2FP20mm3XNYBGaCQx0I1sJtsBZZ
36SW8w23bv2FGPSqgmvZn9mkVL9uqYA7DwvGSSaZDA/4bxhFDq09IQKdyoHZugfM
Mzzr3HRi1nb39TV4b1NISQeW7DSo+kgGymdBQZ6HZZ3cgUopj+MVyMIDHTN7x3m0
mQEa2veb4KO+U3X84NO+Z+lkzDFhi20E7XdmgcRax+cB4vBonI0i/obJ1q2JlP0R
ZPgYfAnK4TVExztOb0wvMhVa5aaU+scBFOSrxy90CE8YfzMNKlTFbbrIc/2EUA8l
DRi7SG1R0wIYsmUY6pI2Yfli8vn9MMauuMwExzgSArar0Pi8EhRKb5EHUI9GMGgu
duhtP7nT/jS1kU2ABq9veD74OQ6PHUiGi+hST7/uqfOUpUApzLZaE/Mt89CGYAnY
SEP9c4qZnWBRrx9orN6PQrT63XJWEe4h2B7xEhTsIMOVYpvzzLsgOsNZEOonsKRx
40s9HBCZLyvPRV6mhdp+//W8LtdudWEmLNbtbVSqFWCqEigDstwXB4xW8ImSRL7M
FGHGxsHVPkRl4q2SwNltSICh54BY82Km2etPBvJTyHpvYz+B2Mc=
=3HZX
-----END PGP SIGNATURE-----

--aq5g2imjziqhhxno--