Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1piFEJ-0002Ra-Qi for pgsql-general@arkaria.postgresql.org; Fri, 31 Mar 2023 13:55:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1piFEI-0003Hn-Gw for pgsql-general@arkaria.postgresql.org; Fri, 31 Mar 2023 13:55:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1piFEI-0003HW-6G for pgsql-general@lists.postgresql.org; Fri, 31 Mar 2023 13:55:46 +0000 Received: from mail-wr1-x42c.google.com ([2a00:1450:4864:20::42c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1piFEB-00056d-Ba for pgsql-general@lists.postgresql.org; Fri, 31 Mar 2023 13:55:45 +0000 Received: by mail-wr1-x42c.google.com with SMTP id t4so17253067wra.7 for ; Fri, 31 Mar 2023 06:55:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; t=1680270936; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=mPKH5GdnOgl1sHH55NCOXEVVUHZUoFpax+jm9vyhK4s=; b=dFUGL7ZgXj6sZBTEMpmHEVV9mLyaLn2hdm3aM7DJKf7FG+4sV0AyGIf+k/N5zgD7RD lCLz+jTI8XvUJtzkACpxm4Okg9LPu1ljypaNUVkHqqcoNogZgw/2zd6OguBYczvrfjIE i1lrq8x/O6RD6Ib9fU68W5Orqur1VjWrTGda6MRvpXUX6IT5OyFdG7mK6vP4f7tms6bp CZshV9j6xV5P1Pa9zIbxyrs9uCxoqeemIovwWliLtGyhV6KQQYDLboZAYQq5ka0JFpZ3 3dVsAXiH0gf4VbUHLWTfeytpECttb26GH6zGatBb0z54Fq/2VhqZnF2+DIpCIMY8N3Jo 9/Xg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680270936; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=mPKH5GdnOgl1sHH55NCOXEVVUHZUoFpax+jm9vyhK4s=; b=VmHRuyfO93ri+PClLoOxuFB0aqB5luH4IrC8RWlDPBW47KZ2TSaxsOv5Gb+wvFb61Y DqQsDrLQyDNKSFAbF2SFDUF5FVhYdLkKVClMWITmQgRzA/1VO2MvCt7Hc5+WaYc8XvKg M3BqCjFzW92DaYnI1iILMRw2CTwEfGReOPgk8K3USo8K09gD+z3I+UpLkIi/H68Rfd05 mbTnx3QRguf+whDoMN+kzy7+GCbEefsraUsyhJVdV3GEPaN1AkPO5l3CXTuTQ+jB+xBF lUZD9lOo2CMq6/H9bPcAO0GWbmguMtCxWJlDqIZKqiYWEB5+1QqGAViPQFfNbcsHbyAu wr0Q== X-Gm-Message-State: AAQBX9chVATSP8zztStpJalT05KcBgZefl4GhlxcxFiDPZ4vAkqLe2zw Q1C+ehBk0H7lB5PXPlPv49CxplMUmUVm69B3BzI= X-Google-Smtp-Source: AKy350YB4chf6uXu1IwcGOvAxd+Q3UBwQq/goAV+4KGslp0eEr2EG0ggw7mrMQ45iezI59Wk+KtHew== X-Received: by 2002:a5d:5943:0:b0:2e4:b9a3:441a with SMTP id e3-20020a5d5943000000b002e4b9a3441amr4357001wri.50.1680270936644; Fri, 31 Mar 2023 06:55:36 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:5099:80d3:ae39:3aca:582b]) by smtp.gmail.com with ESMTPSA id a2-20020adfed02000000b002c55de1c72bsm2252614wro.62.2023.03.31.06.55.36 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 31 Mar 2023 06:55:36 -0700 (PDT) Message-ID: <4fa776a4762bbdf774fb68d51a79cc0f5ca89aff.camel@cybertec.at> Subject: Re: Cannot terminate backend From: Laurenz Albe To: Arnaud Lesauvage , "pgsql-general@lists.postgresql.org" Date: Fri, 31 Mar 2023 15:55:35 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2023-03-31 at 13:46 +0000, Arnaud Lesauvage wrote: > I have a long running query that I seem unable to either cancel or termin= ate. > What could be the reason for this, and what is the bet way to terminate t= his kind of query ? > =C2=A0 > The query is a CTE using postgis ST_ClusterDBSCAN function. The CTE retur= ns approximately 150k rows. > The SQL is as follows :=20 >=20 > EXPLAIN ANALYZE > WITH subq AS ( > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SELECT id, geom, ST_ClusterDBS= CAN(geom, eps :=3D 1000, minpoints :=3D 1) OVER() AS cluster_id > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM mytable > ) > SELECT cluster_id, count(id), ST_Collect(geom)=20 > FROM subq > GROUP BY cluster_id; > =C2=A0 > pg_stat_activity show no wait event. > pg_cancel_backend(mypid) returns true but the state does not change in pg= _stat_activity. > pg_terminate_backend(mypid) yields the same result (as superuser) > Pg_stat_activity show no wait_event. > =C2=A0 > SELECT version(); > PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compi= led by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit The most likely explanation is that one of the PostGIS functions runs for a long time without checking CHECK_FOR_INTERRUPTS(). That would be a PostGIS bug. Try to construct a reproducible test case that you can share! Perhaps this trick can help: https://www.cybertec-postgresql.com/en/cancel-hanging-postgresql-query/ Yours, Laurenz Albe