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 1soKMs-00AKa9-QH for pgsql-general@arkaria.postgresql.org; Wed, 11 Sep 2024 10:14:35 +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 1soKMr-00EEQW-NV for pgsql-general@arkaria.postgresql.org; Wed, 11 Sep 2024 10:14:33 +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 1soKMr-00EELh-C0 for pgsql-general@lists.postgresql.org; Wed, 11 Sep 2024 10:14:33 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1soKMo-000c1g-J9 for pgsql-general@postgresql.org; Wed, 11 Sep 2024 10:14:32 +0000 Received: by mail-ot1-x32e.google.com with SMTP id 46e09a7af769-710ea99f664so92215a34.3 for ; Wed, 11 Sep 2024 03:14:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726049670; x=1726654470; darn=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=EbYTQPp6vHkgvhfjglmmLogvzCcvjyXKQS3MsBW373o=; b=O6VTjsJnSr390JCeNZqsLkC7y12xiLpNMghRMcu/QoAy+WuUxc3+1BYhG7CIIsdf0p TUpm1F6FdjmhJ/nXrNr6+ENKn85soz8zRLfarJlPYpvjJEjYKGPsgX8pSQ+p1f3ks+A6 pihF5DQoF++sgPsNhMN78weTZgIXZEZ1dt/bjPbAoqtJeA4AfFoA9mFIR1t8HX8SHhL+ ZkBUclREtsSqoAhFNKZeSfyFSrecFnseZOKpHKyb0uEJaUwVm4tu5kyXnHZXfCNjJmiX 9Uk5LxTw8YGLXhhwZEerzhEXIBaYYICNMSGeoix8DIWG2TnxzkYTMLw1uanHXMGbEj+t yAcg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726049670; x=1726654470; 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=EbYTQPp6vHkgvhfjglmmLogvzCcvjyXKQS3MsBW373o=; b=MwRuVWHXJ6rw4ckJhT1+kFPK4UXLQ2mMm5b6lwCkSt49uejPC83/8uZll+DFSzibM8 Ot1B7OdobjhL3L3H0TAabG6veZBxfoDO+gbsdDjmZgbUhgaL7V/oytGUnXsZPyIwWRFu oNKcP0B1tgB7ehqqYbOGIiZU4eFbZcXuxwdRYCmRgCmwStC6lD9Z8lAMSNC9I0V1ZRbS 41O0eJ1dSpbpNLrKF6rBbpLPt5KrafVE7FetADoW2CcaoQctJlpSZsOmd1r4wXHKoxa7 CVDTzhPZFYG+77gIY8ZqHsY1W7Aw9qaVvK+VGqiZV7bLdwGD8AuN9dSt8MkrnowA2Yow 2+hw== X-Gm-Message-State: AOJu0YznVWKQJ1ZDr9A23gzf5BuuNDFFK9iXx5ouB03u/sfYrncR7LGU +u/u8QAzaloh69b4GPPerls1LSqy62dFEswx0m3cjUk765dnAAbsbc2XJFO5foNpJFkCYbh5I/W hmnVaAkQbJkcD8a3LL85878icFMDmzA== X-Google-Smtp-Source: AGHT+IEhzVCjabEdbCJ5dKWLFaT3PcxivCqAl+WPQquCvOSByDf9JcNlMZom/9aRmUnBp6OLPMyCnKeBjrXfK+uUxIQ= X-Received: by 2002:a05:6808:a9c:b0:3e0:342f:5fc1 with SMTP id 5614622812f47-3e0342f6383mr4922121b6e.2.1726049670109; Wed, 11 Sep 2024 03:14:30 -0700 (PDT) MIME-Version: 1.0 References: <3147833.1725894012@sss.pgh.pa.us> In-Reply-To: From: Fabrice Chapuis Date: Wed, 11 Sep 2024 12:14:19 +0200 Message-ID: Subject: Re: infinite loop in an update statement To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="00000000000073a56a0621d54117" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000073a56a0621d54117 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Sep 10, 2024 at 4:49=E2=80=AFPM Fabrice Chapuis wrote: > no lock, in view pg_stat_activity > > status =3D active > wait event =3D NULL > wait event type =3D NULL > > On Mon, Sep 9, 2024 at 5:00=E2=80=AFPM Tom Lane wrote= : > >> Fabrice Chapuis writes: >> > why this update does not return instantly? >> >> > UPDATE table_a a >> > SET col1 =3D (SELECT MIN(b.col1) >> > FROM table_b b >> > WHERE b.col2 =3D a.col2) >> >> Maybe query is waiting for a lock on one of those tables? >> >> regards, tom lane >> > --00000000000073a56a0621d54117 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Tue, Sep 10, 2024 at 4:49=E2=80=AF= PM Fabrice Chapuis <fabrice63= 6861@gmail.com> wrote:
no lock, in view pg_stat_activity
=

status =3D active
wait event =3D NULL
wait eve= nt type =3D NULL

On Mon, Sep 9, 2024 at 5:00=E2=80=AFPM Tom Lane <<= a href=3D"mailto:tgl@sss.pgh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us= > wrote:
Fabr= ice Chapuis <fabrice636861@gmail.com> writes:
> why this update does not return instantly?

> UPDATE table_a a
>=C2=A0 SET col1 =3D (SELECT MIN(b.col1)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0FROM table_b b
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0WHERE b.col2 =3D a.col2)

Maybe query is waiting for a lock on one of those tables?

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane
--00000000000073a56a0621d54117--