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 1sTPHJ-00FwRZ-6J for pgsql-general@arkaria.postgresql.org; Mon, 15 Jul 2024 17:14:21 +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 1sTPHF-00BhfE-AH for pgsql-general@arkaria.postgresql.org; Mon, 15 Jul 2024 17:14:17 +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 1sTPHE-00Bhf6-T3 for pgsql-general@lists.postgresql.org; Mon, 15 Jul 2024 17:14:16 +0000 Received: from mail-oa1-x34.google.com ([2001:4860:4864:20::34]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sTPHC-002CFM-9K for pgsql-general@lists.postgresql.org; Mon, 15 Jul 2024 17:14:15 +0000 Received: by mail-oa1-x34.google.com with SMTP id 586e51a60fabf-25e134abf00so2276029fac.1 for ; Mon, 15 Jul 2024 10:14:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721063653; x=1721668453; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=ioyk21ajg/ZNQLNQy33JcrQrtJ1VfMDkw73X+te58tI=; b=jLVNXTHOlKTnLjog58JDUIpqHUg3Bf/hRMp0lAxAA93bEPkeuqwshr4YZ0U6gea1IL a9h27re/zE0T/oDHMou4PalXxQJHHHg+fQj21JHnIJAJK3fJL/FNMdgU3LtZjMkVB1fR XDY1IlA08n6pA0S0FBUak5gMgNFEIYWZJdBo0GqCqz5KCDVDYGq8T5m6iAxl5zTrCQ+s M0nQ8FqeO4kBBmKeNnAdd0i3DHvKAy95hN8z7q5tptgUyBEpVpGXfZ44ZdQwhAbP0zOd eExY9aN2iYExQJeiKacuETrKEp+jWTYE3d4iYpGfkRHpulJ1W2BL4xlIoj/3RyMdG8D8 4ktQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721063653; x=1721668453; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=ioyk21ajg/ZNQLNQy33JcrQrtJ1VfMDkw73X+te58tI=; b=sraDbEte1bGMN+I9TM1uKtPaSX4fEoB1d7/0Xb0kalilvMRuzswh/y5tOCFtt2i0cr SHHlXXDHlH9wiE9yjkUSgNNxT5AxVfLYOuAUIeoNKtkAckLYgy3R89gV7Lpnt3vM24U1 UiG6VepPxh8CQdwEqoAhYIPEJISQtH4ZVwS7liOeSsuoG51NxGRwkuR5f3Ef6w0QjRPP dQ1wCQytdQori+1zBqGIqFvh6jSvdggUnw4WQWpbtUNzIlM9AthPdRZY6LZPbPlBfgyW OEM71nqwnjUjFbMfuncomXV+oqGbsvgEJNNbQgrmQFMB3VOvmYO4u5TCgXmhhLyZThsK eRhA== X-Gm-Message-State: AOJu0YyzJshag0jQDiDTuu01DEgdZSpAGMiDdk8nPF1UYfC54PJgbmZh Y5YQO5l8I7Vpd5JEGGMJZ7scJMHTGqcZnyNWSTrS0OdVpGdMfSWHwTd87VbrwBeAC5iUxGbZhqR /qiuZXNSBl8r8GgfNHiIgxeB+YQ0i9Q== X-Google-Smtp-Source: AGHT+IFM2GxBnk8CWYoXSLYLIFKh4T5KRIMBN1B2FNixMAnJ5O/hnk3lhj8We5BYBiNigjJnjTXscWg8TpuB73JaUBs= X-Received: by 2002:a05:6870:10cd:b0:254:9501:db82 with SMTP id 586e51a60fabf-25eaec601efmr11785654fac.52.1721063653025; Mon, 15 Jul 2024 10:14:13 -0700 (PDT) MIME-Version: 1.0 References: <20240715143518.3v5xdfuj27ryzuzh@hjp.at> <4ff0dd59-9d7b-4a13-ad75-3627080458e1@aklaver.com> <28c88e41-36bc-4704-9c30-f81986f6cdc8@aklaver.com> In-Reply-To: From: Ron Johnson Date: Mon, 15 Jul 2024 13:14:01 -0400 Message-ID: Subject: Re: How does this FK constraint error happen? To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000acb271061d4c5b09" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000acb271061d4c5b09 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jul 15, 2024 at 12:47=E2=80=AFPM Adrian Klaver wrote: > On 7/15/24 09:21, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 11:37=E2=80=AFAM Adrian Klaver > > > wrote: > > > > > > I don't think it is entirely coincidental that 1210 is the only sho= wn > > user_id with a modified_on value that is in proximity to the delete > > error. > > > > > > I don't think so either. > > > > My suspicion is that actions are not happening in the exact order > > you think they are. > > > > > > modified_on is CURRENT_TIMESTAMP or NOW() or somesuch. I'm not sure, > > because I'm not privy to the code. > > > > But I'm printing the system time in bash before every statement. > > That is why I wrote 'Time travel?'. > > I suspect the modified_on time in the table is not accurately > representing when the row is modified. > That JBDC code is pretty slow... > > > > > I would think that combining DELETE FROM > > rel_group_user; and DELETE FROM public.access_user; in a single > > transaction would be a good start to fixing this. > > > > > > That is in fact what I'm working on now. There are 26 tables, and they > > must be done in a specific order when deleting, and the reverse while > > inserting. > > > > postgres_fdw would make this easier... > > It can't be installed? > Less bureaucratic overhead to write a script. --000000000000acb271061d4c5b09 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Jul 15, 2024 at 12:47=E2=80=AFPM = Adrian Klaver <adrian.klave= r@aklaver.com> wrote:
On 7/15/24 09:21, Ron Johnson wrote= :
> On Mon, Jul 15, 2024 at 11:37=E2=80=AFAM Adrian Klaver
> <adr= ian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>


>=C2=A0 =C2=A0 =C2=A0I don't think it is entirely coincidental that = 1210 is the only shown
>=C2=A0 =C2=A0 =C2=A0user_id with a modified_on value that is in proximi= ty to the delete
>=C2=A0 =C2=A0 =C2=A0error.
>
>
> I don't think so either.
>
>=C2=A0 =C2=A0 =C2=A0My suspicion is that actions are not happening in t= he exact order
>=C2=A0 =C2=A0 =C2=A0you think they are.
>
>
> modified_on is CURRENT_TIMESTAMP or NOW() or somesuch.=C2=A0 I'm n= ot sure,
> because I'm not privy to the code.
>
> But I'm printing the system time in bash before=C2=A0every stateme= nt.

That is why I wrote 'Time travel?'.

I suspect the modified_on time in the table is not accurately
representing when the row is modified.

= That JBDC=C2=A0code is pretty slow...
=C2=A0

>
>=C2=A0 =C2=A0 =C2=A0I would think that combining DELETE FROM
>=C2=A0 =C2=A0 =C2=A0rel_group_user; and DELETE FROM public.access_user;= in a single
>=C2=A0 =C2=A0 =C2=A0transaction would be a good start to fixing this. >
>
> That is in fact what I'm working on now.=C2=A0 There are 26 tables= , and they
> must be done in a specific order when deleting, and the reverse while =
> inserting.
>
> postgres_fdw would make this easier...

It can't be installed?

Less bureauc= ratic overhead to write a script.

--000000000000acb271061d4c5b09--