pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCannot terminate backend
5+ messages / 3 participants
[nested] [flat]
* Cannot terminate backend
@ 2023-03-31 13:46 Arnaud Lesauvage <arnaud.listes@codata.eu>
0 siblings, 1 reply; 5+ messages in thread
From: Arnaud Lesauvage @ 2023-03-31 13:46 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi all,
I have a long running query that I seem unable to either cancel or terminate.
What could be the reason for this, and what is the bet way to terminate this kind of query ?
The query is a CTE using postgis ST_ClusterDBSCAN function. The CTE returns approximately 150k rows.
The SQL is as follows :
EXPLAIN ANALYZE
WITH subq AS (
SELECT id, geom, ST_ClusterDBSCAN(geom, eps := 1000, minpoints := 1) OVER() AS cluster_id
FROM mytable
)
SELECT cluster_id, count(id), ST_Collect(geom)
FROM subq
GROUP BY cluster_id;
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.
SELECT version();
PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
Any idea ? Thanks a lot !
Regards
Arnaud
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cannot terminate backend
@ 2023-03-31 13:55 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Arnaud Lesauvage <arnaud.listes@codata.eu>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-03-31 13:55 UTC (permalink / raw)
To: Arnaud Lesauvage <arnaud.listes@codata.eu>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 terminate.
> What could be the reason for this, and what is the bet way to terminate this kind of query ?
>
> The query is a CTE using postgis ST_ClusterDBSCAN function. The CTE returns approximately 150k rows.
> The SQL is as follows :
>
> EXPLAIN ANALYZE
> WITH subq AS (
> SELECT id, geom, ST_ClusterDBSCAN(geom, eps := 1000, minpoints := 1) OVER() AS cluster_id
> FROM mytable
> )
> SELECT cluster_id, count(id), ST_Collect(geom)
> FROM subq
> GROUP BY cluster_id;
>
> 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.
>
> SELECT version();
> PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Cannot terminate backend
@ 2023-03-31 14:41 Arnaud Lesauvage <arnaud.listes@codata.eu>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Arnaud Lesauvage @ 2023-03-31 14:41 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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
> terminate.
> > What could be the reason for this, and what is the bet way to terminate
> this kind of query ?
> >
> > The query is a CTE using postgis ST_ClusterDBSCAN function. The CTE
> returns approximately 150k rows.
> > The SQL is as follows :
> >
> > EXPLAIN ANALYZE
> > WITH subq AS (
> > SELECT id, geom, ST_ClusterDBSCAN(geom, eps := 1000, minpoints
> > := 1) OVER() AS cluster_id
> > FROM mytable
> > )
> > SELECT cluster_id, count(id), ST_Collect(geom) FROM subq GROUP BY
> > cluster_id;
> >
> > 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.
> >
> > SELECT version();
> > PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
> > compiled 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/
Thanks Laurenz
Unfortunately, I don't have a shell access to the server, so I guess I'll have to ask to sysadmin to kill -9 ?
Regards
Arnaud
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cannot terminate backend
@ 2023-03-31 15:57 Paul Ramsey <pramsey@cleverelephant.ca>
parent: Arnaud Lesauvage <arnaud.listes@codata.eu>
0 siblings, 1 reply; 5+ messages in thread
From: Paul Ramsey @ 2023-03-31 15:57 UTC (permalink / raw)
To: Arnaud Lesauvage <arnaud.listes@codata.eu>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Afraid so. You can wait longer, I guess. You may have found two bugs… the lack of an interrupt in the dbscan loop, which I’m working on now. And maybe an infinite looping case? In which case if you want a fix on that, you’ll have to share your data and query.
P.
> On Mar 31, 2023, at 7:41 AM, Arnaud Lesauvage <arnaud.listes@codata.eu> wrote:
>
>> 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
>> terminate.
>>> What could be the reason for this, and what is the bet way to terminate
>> this kind of query ?
>>>
>>> The query is a CTE using postgis ST_ClusterDBSCAN function. The CTE
>> returns approximately 150k rows.
>>> The SQL is as follows :
>>>
>>> EXPLAIN ANALYZE
>>> WITH subq AS (
>>> SELECT id, geom, ST_ClusterDBSCAN(geom, eps := 1000, minpoints
>>> := 1) OVER() AS cluster_id
>>> FROM mytable
>>> )
>>> SELECT cluster_id, count(id), ST_Collect(geom) FROM subq GROUP BY
>>> cluster_id;
>>>
>>> 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.
>>>
>>> SELECT version();
>>> PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
>>> compiled 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/
>
>
> Thanks Laurenz
> Unfortunately, I don't have a shell access to the server, so I guess I'll have to ask to sysadmin to kill -9 ?
>
> Regards
> Arnaud
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Cannot terminate backend
@ 2023-04-03 07:12 Arnaud Lesauvage <arnaud.listes@codata.eu>
parent: Paul Ramsey <pramsey@cleverelephant.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Arnaud Lesauvage @ 2023-04-03 07:12 UTC (permalink / raw)
To: Paul Ramsey <pramsey@cleverelephant.ca>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> From: Paul Ramsey <pramsey@cleverelephant.ca>
> Afraid so. You can wait longer, I guess. You may have found two bugs… the lack of an interrupt in the dbscan loop, which I’m working on now. And maybe an infinite looping case? In which case if you want a fix on that, you’ll have to share your data and query.
I let the query run over the week-end and they are now gone, so I guess your dbscan fix is what I was missing. Thanks !
Regards
Arnaud
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-04-03 07:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-31 13:46 Cannot terminate backend Arnaud Lesauvage <arnaud.listes@codata.eu>
2023-03-31 13:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-31 14:41 ` Arnaud Lesauvage <arnaud.listes@codata.eu>
2023-03-31 15:57 ` Paul Ramsey <pramsey@cleverelephant.ca>
2023-04-03 07:12 ` Arnaud Lesauvage <arnaud.listes@codata.eu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox