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 1sIBWH-00DipM-JN for pgsql-admin@arkaria.postgresql.org; Fri, 14 Jun 2024 18:19:25 +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 1sIBWE-004RWM-Ln for pgsql-admin@arkaria.postgresql.org; Fri, 14 Jun 2024 18:19:23 +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 1sIBWE-004RW3-60 for pgsql-admin@lists.postgresql.org; Fri, 14 Jun 2024 18:19:23 +0000 Received: from mail-qk1-x732.google.com ([2607:f8b0:4864:20::732]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sIBWC-001Jhh-FE for pgsql-admin@lists.postgresql.org; Fri, 14 Jun 2024 18:19:21 +0000 Received: by mail-qk1-x732.google.com with SMTP id af79cd13be357-797e2834c4eso202651985a.0 for ; Fri, 14 Jun 2024 11:19:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aweber.com; s=2018090500; t=1718389159; x=1718993959; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=ljsw2g+bI4yqC/7lDyNXltGjTb4K46atR4OTSG/3Y88=; b=ak+zCr5HqAVkrvQhQhVwwr43nEwzJynE5ZEXvk7XWJIGJioFcTR2UToj6zXE5ixNxO zi6c1fUYYvxxIp02sjQQxJ3dV9YDqJossVns6QHhjgAuNNf6HFuTAec7ORKjzmLi7HGp TFdxLeetom10WDECOEWIBkkpvay9j0iogcVQy3oZfrMliw25cG7ZFuFWRIeD51NeMiPL wzeJSs31rMlwkeE35oHqSqXmbC6/dzJnNqFAirCz7SSBiSq1C8CiGEb5iTHFYfGz4X+4 uvfY7sHy3+6pSw8CwTI6BL4FURSirNPoZUQHwH0t4JoqpYMx1N9Az/0muwKtI3+yOoBO WN8g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718389159; x=1718993959; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=ljsw2g+bI4yqC/7lDyNXltGjTb4K46atR4OTSG/3Y88=; b=nHbYHBNCpnfbG+OE/NfRwN0JuFRtNAAxKP/m2oY/VsW7FSt8YoxYlFMIxEJgX4t9ZZ L5HTvjTmHqCzZTmRHKFdFPZYKff/BLs4GK2uH1HY1H6tlhf+iHtavVHFSmKh8tm4wTSi 0PCN/cZNlUx4eGC26ncK1+pjGSgkNBkyTgfwssVGhJsMJI3YCepTxDtVsrjE/zDAf9zJ j12fl0IwfdHko5g37iPXpNHM1ac8XkZOqdv9O3WlyNyBoWsWxKPpB6LtIw1hhS6SpREg yMI0XTbkY3/ZLooF5xm21TlRnc+8A19N82d0St9gu542J5K6guOt2GuHLgoG5XDGaTZg Uttw== X-Gm-Message-State: AOJu0YySpHdDjFpwLjDZl4IoApBVPbQchoNZ0VkVn4AX3j1bSD6HA0Wh 559v5eYEjma1piJH3Ep/dBs4mqzlYU8tPPfTLeckggMaBgsSMo60X8fc0tZyXMQ= X-Google-Smtp-Source: AGHT+IGxFj/GSqbgrswCyej0Q8Wvjchdh24if1nV2b/kNxyqUeo6LVUDvkiU40Cd6x7wncElLWEjIg== X-Received: by 2002:a05:620a:2b4e:b0:795:890c:3f57 with SMTP id af79cd13be357-798d03e693dmr561372585a.37.1718389159417; Fri, 14 Jun 2024 11:19:19 -0700 (PDT) Received: from smtpclient.apple ([172.59.218.20]) by smtp.gmail.com with ESMTPSA id af79cd13be357-798aaecc004sm168972885a.31.2024.06.14.11.19.18 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 14 Jun 2024 11:19:19 -0700 (PDT) From: Chris Hoover Message-Id: <464C0807-4299-42B4-81E8-AC8D016C3AC7@aweber.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_1DD26E8B-EC73-4956-AD19-0C8541378FD4" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\)) Subject: Re: Outage caused by query in IPC:MessageQueueReceive wait???? Date: Fri, 14 Jun 2024 14:19:06 -0400 In-Reply-To: Cc: pgsql-admin@lists.postgresql.org To: Ozgur Kulu References: <33F68D47-1D2D-4DB8-B753-BE83A3FA653E@aweber.com> X-Mailer: Apple Mail (2.3774.500.171.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_1DD26E8B-EC73-4956-AD19-0C8541378FD4 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Yes, it appeared to be in a transaction. The = pg_stat_activity.xact_start was before the query_start. Unfortunately, = I don=E2=80=99t have the other queries. (I snapshot pg_stat_activity = every minute, so it missed any prior queries to the blocker). Thanks, Chris Hoover Senior DBA AWeber.com Cell: (803) 528-2269 Email: chrish@aweber.com > On Jun 14, 2024, at 2:16=E2=80=AFPM, Ozgur Kulu = wrote: >=20 > Hi chris,=20 >=20 > Is this query in transaction block ?=20 >=20 > 14 Haz 2024 Cum 20:03 tarihinde Chris Hoover > =C5=9Funu yazd=C4=B1: >> Forgot to mention, this is on PG 16.1 >>=20 >> Thanks, >>=20 >>=20 >> Chris Hoover >> Senior DBA >> AWeber.com >> Cell: (803) 528-2269 >> Email: chrish@aweber.com >>=20 >>=20 >>=20 >>> On Jun 14, 2024, at 12:52=E2=80=AFPM, Chris Hoover = > wrote: >>>=20 >>> All, >>>=20 >>> We had an outage today that appears to have been caused by a query = in "IPC:MessageQueueReceive=E2=80=9D wait state blocking another query = from completing a truncate. =20 >>>=20 >>> Is there anyway to figure out what exactly it was waiting on other = than an IPC message? I=E2=80=99m trying to get to the true root cause = of our outage. >>>=20 >>> Thanks, >>>=20 >>>=20 >>> Chris Hoover >>> Senior DBA >>> AWeber.com >>> Cell: (803) 528-2269 >>> Email: chrish@aweber.com >>>=20 >>>=20 >>>=20 >>=20 --Apple-Mail=_1DD26E8B-EC73-4956-AD19-0C8541378FD4 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Yes,  it = appeared to be in a transaction.  The pg_stat_activity.xact_start = was before the query_start.  Unfortunately, I don=E2=80=99t have = the other queries.  (I snapshot pg_stat_activity every minute, so = it missed any prior queries to the blocker).

Thanks,


Chris = Hoover
Senior DBA
AWeber.com
Cell: (803) = 528-2269
Email: chrish@aweber.com



On Jun 14, 2024, at 2:16=E2=80=AFP= M, Ozgur Kulu <kuluozgur@gmail.com> wrote:

Hi = chris, 

Is this query = in transaction block ? 

14 Haz 2024 = Cum 20:03 tarihinde Chris Hoover <chrish@aweber.com> =C5=9Funu = yazd=C4=B1:
Forgot to mention, this is on PG = 16.1

Thanks,


Chris = Hoover
Senior DBA
AWeber.com
Cell: (803) = 528-2269



On Jun 14, 2024, at 12:52=E2=80=AF= PM, Chris Hoover <chrish@aweber.com> = wrote:

All,

We = had an outage today that appears to have been caused by a query in = "IPC:MessageQueueRe= ceive=E2=80=9D wait state blocking another query from completing a = truncate.  

<= /span>
Is there = anyway to figure out what exactly it was waiting on other than an IPC = message?  I=E2=80=99m trying to get to the true root cause of our = outage.

Thanks,


Chris = Hoover
Senior DBA
AWeber.com
Cell: (803) = 528-2269


=


= --Apple-Mail=_1DD26E8B-EC73-4956-AD19-0C8541378FD4--