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 1sTNTW-00FczW-Ch for pgsql-general@arkaria.postgresql.org; Mon, 15 Jul 2024 15:18:50 +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 1sTNTU-00Axzk-LF for pgsql-general@arkaria.postgresql.org; Mon, 15 Jul 2024 15:18:48 +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 1sTNTU-00Axzb-5p for pgsql-general@lists.postgresql.org; Mon, 15 Jul 2024 15:18:48 +0000 Received: from mail-oa1-x29.google.com ([2001:4860:4864:20::29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sTNTN-002BU7-5e for pgsql-general@lists.postgresql.org; Mon, 15 Jul 2024 15:18:46 +0000 Received: by mail-oa1-x29.google.com with SMTP id 586e51a60fabf-25e3d8db819so2041215fac.0 for ; Mon, 15 Jul 2024 08:18:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721056720; x=1721661520; 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=jF71RYYlNrowhqA9k9oy9jRzwfUMmbif3MYweM0temo=; b=JmUER9mx6noTBnAnSQgmkgXUEnBP3mQhXq/ZHyx8XvXrkTUCk1FhuURxp7gmWjL/D7 okQoyc0nP7M6F5p49/eLdgV9PUnKhGcA0yMGkj1pN8vtbhr3cquYG4EYO1iuQZQXbzXb +Ax0T5iYcNddIsYnuA5SWlKyo7VK+CKFR6HhwfYJHUGXK9B/sIL31gKmmBLjYq7ZmjgG lvkJ8/Zb0FwPajK/Va3qPM8kiHwrExMWbt2SiEm0IvCls04Ly2g9gn4gQUCZ1bHjAtev bVsDdmYEl0JhN2+rlR7s1ml7h7CmaLsfg7kvA0JsBWVcBrojqOGQNZCk1tkMU1ZmjyVu VcTg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721056720; x=1721661520; 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=jF71RYYlNrowhqA9k9oy9jRzwfUMmbif3MYweM0temo=; b=m93v74QxoJqKk1xuZfOleyuURR7hnGiCFQ3RbZdfh5M4pphZqB0HUlsZ3lK12JEht2 v6rvwhKZx2Nr35P7swwlxMIpGWw4QwuUaCl2g0n5XL+qq26ZnJuqMfh+S++fAkQrbvYI +6RrHiKn2qK8QfO2tTkO4nhiiH2cYSeKYotOYE8OBFbjpXPeDPDyfqgTVXHJ3SK7nTQ5 uyG5BTXHTh4dpJHldE6O0VTrRSSFzeZHKVpn2wNMvI2S2660pvnGwnd2Hgv1EIMPICyY 72uMWQOLkeG5bQl328HWZThPeLwBRX0GFm0eK+4xnR0PlpwbOH7Pd/X8jUJhjtcjAoZ+ LRtA== X-Gm-Message-State: AOJu0Yyx+qMLu3KzCwf2aJMYnt9eAcrOeR9UeTi+54jzf4tC7/wuPlSA LxkaMp2dzniGeIG2+BhsV6J/vL1YaED6yN31sK98Nywj0KcZSDTlLN88i0TbjZRQS7AfAiJDqRr YAz98eM7yev4DLTiq4badARhuQgdrCQ== X-Google-Smtp-Source: AGHT+IF1rOwGUErMItZlNJSTmRih6NLknA8sOeK93OnkJLyv1E9LHGqnwrQbUhgNnTEf/FCOQLGxUai8DSXrAmtW54s= X-Received: by 2002:a05:6870:ac26:b0:23c:bc3a:6ccb with SMTP id 586e51a60fabf-25eae7baa68mr17095483fac.19.1721056720160; Mon, 15 Jul 2024 08:18:40 -0700 (PDT) MIME-Version: 1.0 References: <20240715143518.3v5xdfuj27ryzuzh@hjp.at> <4ff0dd59-9d7b-4a13-ad75-3627080458e1@aklaver.com> In-Reply-To: <4ff0dd59-9d7b-4a13-ad75-3627080458e1@aklaver.com> From: Ron Johnson Date: Mon, 15 Jul 2024 11:18:29 -0400 Message-ID: Subject: Re: How does this FK constraint error happen? To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000719078061d4abe4f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000719078061d4abe4f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jul 15, 2024 at 11:06=E2=80=AFAM Adrian Klaver wrote: > On 7/15/24 07:53, Ron Johnson wrote: > > On Mon, Jul 15, 2024 at 10:35=E2=80=AFAM Peter J. Holzer > > wrote: > > [snip] > > > > > > Is it possible that some other process created an entry in > > rel_group_user between these two queries? > > > > That was, in fact, the problem. At just the wrong time to impact one o= f > > the child databases (TAPd), but not the other two (TAPb and TAPc). > > > > TAPd=3D# select * from rel_group_user > > where user_id between 1100 and 1300 > > order by user_id; > > user_id | group_id | modified_by | modified_on > > ---------+----------+-------------+------------------------- > > 1133 | 2 | 1133 | 2024-07-15 08:43:35.669 > > 1142 | 2 | 1142 | 2024-07-15 09:05:58.451 > > 1147 | 2 | 1147 | 2024-07-15 09:30:37.169 > > 1158 | 2 | 1158 | 2024-07-15 09:36:45.142 > > 1197 | 2 | 1197 | 2024-07-15 09:52:58.477 > > 1210 | 2 | 1210 | 2024-07-15 02:42:09.355 > <<<<<<<<<<<<< > > Time travel? > =F0=9F=98=9E > > 2024-07-15 02:41:15 Deleting from FISPTAPPGS401DA/TAPd.public.access_user > DELETE FROM public.access_user; > > Or do the cron jobs take that long to execute? > The deletes from 26*3 tables (the same 26 tables in three children) took from 02:40:02 to 02:41:47. Then a bunch of COPY statements run (pg_dump from the federation master, then COPY to the federation children). Must be done in a specific order. How is modified_on created? > It's updated by the application. > > 1229 | 2 | 1229 | 2024-07-15 08:33:48.443 > > 1242 | 2 | 1242 | 2024-07-15 10:29:51.176 > > 1260 | 2 | 1260 | 2024-07-15 07:36:21.182 > > 1283 | 2 | 1283 | 2024-07-15 09:48:25.214 > > 1288 | 2 | 1288 | 2024-07-15 08:10:33.609 > > (11 rows) > > > > TAPd=3D# select user_id, login_id, created_on, modified_on > > TAPd-# from public.access_user > > TAPd-# where user_id =3D 1210; > > user_id | login_id | created_on | modified_on > > > ---------+------------+-------------------------+------------------------= - > > 1210 | JORIEUSER3 | 2023-10-20 11:54:24.562 | 2024-07-15 > 02:42:09.355 > > (1 row) > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000719078061d4abe4f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Jul 15, 2024 at 11:06=E2=80=AFAM = Adrian Klaver <adrian.klave= r@aklaver.com> wrote:
On 7/15/24 07:53, Ron Johnson wrote= :
> On Mon, Jul 15, 2024 at 10:35=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at
> <mailto:hjp-p= gsql@hjp.at>> wrote:
> [snip]
>
>
>=C2=A0 =C2=A0 =C2=A0Is it possible that some other process created an e= ntry in
>=C2=A0 =C2=A0 =C2=A0rel_group_user between these two queries?
>
> That was, in fact, the problem.=C2=A0 At just the wrong time to impact= one of
> the child databases (TAPd), but not the other two (TAPb and TAPc).
>
> TAPd=3D# select * from rel_group_user
> where user_id between 1100 and 1300
> order by user_id;
>=C2=A0 =C2=A0user_id | group_id | modified_by | =C2=A0 =C2=A0 =C2=A0 mo= dified_on
> ---------+----------+-------------+-------------------------
>=C2=A0 =C2=A0 =C2=A0 1133 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01133 | 2024-07-15 08:43:35.669
>=C2=A0 =C2=A0 =C2=A0 1142 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01142 | 2024-07-15 09:05:58.451
>=C2=A0 =C2=A0 =C2=A0 1147 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01147 | 2024-07-15 09:30:37.169
>=C2=A0 =C2=A0 =C2=A0 1158 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01158 | 2024-07-15 09:36:45.142
>=C2=A0 =C2=A0 =C2=A0 1197 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01197 | 2024-07-15 09:52:58.477
>=C2=A0 =C2=A0 =C2=A0 1210 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01210 | 2024-07-15 02:42:09.355=C2=A0 <<<<<&= lt;<<<<<<<

Time travel?

=F0=9F=98=9E
=C2=A0

2024-07-15 02:41:15 Deleting from FISPTAPPGS401DA/TAPd.public.access_user DELETE FROM public.access_user;

Or do the cron jobs take that long to execute?

The deletes from 26*3 tables (the same 26 tables in three children)= took from 02:40:02 to 02:41:47.
=C2=A0
Then a bunch of= COPY statements run (pg_dump from the federation master, then COPY to the = federation children).=C2=A0 Must be done in a specific order.
How is modified_on= created?

It's updated by the appli= cation.
=C2=A0
>=C2=A0 =C2=A0 =C2=A0 1229 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 = =C2=A0 =C2=A0 =C2=A01229 | 2024-07-15 08:33:48.443
>=C2=A0 =C2=A0 =C2=A0 1242 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01242 | 2024-07-15 10:29:51.176
>=C2=A0 =C2=A0 =C2=A0 1260 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01260 | 2024-07-15 07:36:21.182
>=C2=A0 =C2=A0 =C2=A0 1283 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01283 | 2024-07-15 09:48:25.214
>=C2=A0 =C2=A0 =C2=A0 1288 | =C2=A0 =C2=A0 =C2=A0 =C2=A02 | =C2=A0 =C2= =A0 =C2=A0 =C2=A01288 | 2024-07-15 08:10:33.609
> (11 rows)
>
> TAPd=3D# select user_id, login_id, created_on, modified_on
> TAPd-# from public.access_user
> TAPd-# where user_id =3D 1210;
>=C2=A0 =C2=A0user_id | =C2=A0login_id =C2=A0| =C2=A0 =C2=A0 =C2=A0 crea= ted_on =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 modified_on
> ---------+------------+-------------------------+---------------------= ----
>=C2=A0 =C2=A0 =C2=A0 1210 | JORIEUSER3 | 2023-10-20 11:54:24.562 | 2024= -07-15 02:42:09.355
> (1 row)

--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000719078061d4abe4f--