Re: stopping processes, preventing connections

On Mar 3, 2010, at 18:01 , Josh Kupershmidt wrote:

> Though next time you see a query which doesn't respond to pg_cancel_backend(), try gathering information about the query and what the backend is doing; either you're doing something unusual (e.g. an app is restarting the query automatically after getting canceled) or perhaps you've stumbled on a bug in Postgres.

Hi. A long time has passed since you made that suggestion, but today we stumbled again on a query that wouldn't be canceled. Not only does it not respond to pg_cancel_backend(), it also doesn't respond to kill -SIGTERM.

The query is:

select date_trunc('day',rb.time_stamp),count(*),rb.category,channels.channel_id,channels.name as channel,platforms.platform_id,
platforms.name,rb.operator,item,delivered,msisdn

from public.rb__view as rb,channels,platforms
where
rb.channel_id=channels.channel_id
and rb.platform_id=platforms.platform_id

and rb.time_stamp>='2010-03-14'::date and rb.time_stamp<'2010-03-14'::date + interval '1 day'
and platforms.platform_id=262
and channels.channel_id=1

group by date_trunc('day',rb.time_stamp),rb.category,channels.channel_id,channel,operator,item,delivered,msisdn,platforms.platform_id,
platforms.name

This is nothing too fancy - just an aggregate with group by. And the application on the other side is Crystal Reports, connecting using ODBC. I don't believe the application does anything like restart after cancel, because most of our queries can easily be cancelled and I don't think Crystal has different behaviors for different queries.

rb__view is a union all between two tables (rb and rb__archive) which have the same schema - one holds data from the past 7 weeks and the other holds older data. The channels and platforms tables are basically lookup tables. The fields item,delivered and msisdn all belong to rb__view.

There is nothing in the PostgreSQL log.

If it helps any, this is the EXPLAIN output for the above query. Note that at this time, the query has been running for over a hour and a half.

HashAggregate (cost=221312.77..221318.08 rows=354 width=94)
-> Nested Loop (cost=8078.83..221215.50 rows=3537 width=94)
-> Seq Scan on channels (cost=0.00..3.81 rows=1 width=16)
Filter: (channel_id = 1::numeric)
-> Nested Loop (cost=8078.83..221167.48 rows=3537 width=85)
-> Index Scan using platforms_pkey on platforms (cost=0.00..6.27 rows=1 width=19)
Index Cond: (platform_id = 262::numeric)
-> Append (cost=8078.83..221125.84 rows=3537 width=73)
-> Bitmap Heap Scan on rb (cost=8078.83..221115.42 rows=3536 width=72)
Recheck Cond: ((public.rb.time_stamp >= '2010-03-14'::date) AND (public.rb.time_stamp < '2010-03-15 00:00:00'::timestamp without time zone))
Filter: ((public.rb.channel_id = 1::numeric) AND (public.rb.platform_id = 262::numeric))
-> Bitmap Index Scan on rb_timestamp_ind (cost=0.00..8077.94 rows=104502 width=0)
Index Cond: ((public.rb.time_stamp >= '2010-03-14'::date) AND (public.rb.time_stamp < '2010-03-15 00:00:00'::timestamp without time zone))
-> Index Scan using rba_timestamp_ind on rb__archive (cost=0.00..10.42 rows=1 width=73)
Index Cond: ((rb__archive.time_stamp >= '2010-03-14'::date) AND (rb__archive.time_stamp < '2010-03-15 00:00:00'::timestamp without time zone))
Filter: ((rb__archive.channel_id = 1::numeric) AND (rb__archive.platform_id = 262::numeric))

I don't know what other information I may glean for this. Any thoughts?

Thank you,
Herouth