Lists: | pgsql-admin |
---|
From: | Marc Cousin <mcousin(at)sigma(dot)fr> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | can't cancel a query with pg_cancel_backend |
Date: | 2009-05-05 13:36:52 |
Message-ID: | 200905051536.52251.mcousin@sigma.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Hi, I'm having a problem cancelling a query.
I've already had sometimes this kind of problem, but not this hard (and I
didn't find anything searching the archives, but still I remember seeing
discussions about this). Usually, it cancels in a few seconds to a minute.
I've run this query an hour ago (very simple one, on a bacula database) :
SELECT * from file where pathid = 120;
The database is quite big, file is nearly one billion records.
There is no index with first column on pathid.
Here is the table:
bacula=# \d file
Table "public.file"
Column | Type | Modifiers
------------+---------+-------------------------------------------------------
fileid | bigint | not null default nextval('file_fileid_seq'::regclass)
fileindex | integer | not null default 0
jobid | integer | not null
pathid | integer | not null
filenameid | integer | not null
markid | integer | not null default 0
lstat | text | not null
md5 | text | not null
Indexes:
"file_pkey" UNIQUE, btree (fileid)
"file_fp_idx" btree (filenameid, pathid)
"file_jpfid_idx" btree (jobid, pathid, filenameid)
This is the query plan (so it goes with the third index):
--------------------------------------------------------------------------------------
Index Scan using file_jpfid_idx on file (cost=0.00..7327212.07 rows=2128
width=104)
Index Cond: (pathid = 120)
(2 rows)
When I remembered I didn't have a good index, I tried to cancel the query, but
I can't ( I tried with Ctrl+C from my psql client, then with
pg_cancel_backend in another session, then with sigterm, I know this one
isn't supported, but it didn't solve the problem either...)
Is this an expected behavior ?
Database is 8.3.5 on debian lenny.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marc Cousin <mcousin(at)sigma(dot)fr> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: can't cancel a query with pg_cancel_backend |
Date: | 2009-05-05 14:35:11 |
Message-ID: | 16359.1241534111@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Marc Cousin <mcousin(at)sigma(dot)fr> writes:
> [ can't cancel this query ]
> Index Scan using file_jpfid_idx on file (cost=0.00..7327212.07 rows=2128
> width=104)
> Index Cond: (pathid = 120)
> "file_jpfid_idx" btree (jobid, pathid, filenameid)
Hmm ... is it likely that index entries with pathid = 120 are *very* few
and far between in jobid order? It looks like we have no
CHECK_FOR_INTERRUPTS calls inside the loop in _bt_next(), which is
probably a mistake ...
regards, tom lane
From: | Marc Cousin <mcousin(at)sigma(dot)fr> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: can't cancel a query with pg_cancel_backend |
Date: | 2009-05-05 14:48:12 |
Message-ID: | 200905051648.12707.mcousin@sigma.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
On Tuesday 05 May 2009 16:35:11 Tom Lane wrote:
> Marc Cousin <mcousin(at)sigma(dot)fr> writes:
> > [ can't cancel this query ]
> >
> > Index Scan using file_jpfid_idx on file (cost=0.00..7327212.07
> > rows=2128 width=104)
> > Index Cond: (pathid = 120)
> >
> > "file_jpfid_idx" btree (jobid, pathid, filenameid)
>
> Hmm ... is it likely that index entries with pathid = 120 are *very* few
> and far between in jobid order? It looks like we have no
> CHECK_FOR_INTERRUPTS calls inside the loop in _bt_next(), which is
> probably a mistake ...
>
> regards, tom lane
They are very few and far between...
In fact. there are none, as I had just removed them and I wasn't sure of it,
so I was double-checking before telling my colleagues it was OK :)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marc Cousin <mcousin(at)sigma(dot)fr> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: can't cancel a query with pg_cancel_backend |
Date: | 2009-05-05 15:11:35 |
Message-ID: | 17178.1241536295@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Marc Cousin <mcousin(at)sigma(dot)fr> writes:
> On Tuesday 05 May 2009 16:35:11 Tom Lane wrote:
>> Hmm ... is it likely that index entries with pathid = 120 are *very* few
>> and far between in jobid order? It looks like we have no
>> CHECK_FOR_INTERRUPTS calls inside the loop in _bt_next(), which is
>> probably a mistake ...
> In fact. there are none, as I had just removed them and I wasn't sure of it,
> so I was double-checking before telling my colleagues it was OK :)
OK, that explains it then :-(. I'll see about fixing this.
regards, tom lane
From: | Marc Cousin <cousinmarc(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: can't cancel a query with pg_cancel_backend |
Date: | 2009-05-05 16:27:47 |
Message-ID: | 200905051827.48050.cousinmarc@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
great, thanks a lot...
On Tuesday 05 May 2009 17:11:35 Tom Lane wrote:
> Marc Cousin <mcousin(at)sigma(dot)fr> writes:
> > On Tuesday 05 May 2009 16:35:11 Tom Lane wrote:
> >> Hmm ... is it likely that index entries with pathid = 120 are *very* few
> >> and far between in jobid order? It looks like we have no
> >> CHECK_FOR_INTERRUPTS calls inside the loop in _bt_next(), which is
> >> probably a mistake ...
> >
> > In fact. there are none, as I had just removed them and I wasn't sure of
> > it, so I was double-checking before telling my colleagues it was OK :)
>
> OK, that explains it then :-(. I'll see about fixing this.
>
> regards, tom lane