Обсуждение: can't cancel a query with pg_cancel_backend
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.
Marc Cousin <mcousin@sigma.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
On Tuesday 05 May 2009 16:35:11 Tom Lane wrote: > Marc Cousin <mcousin@sigma.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 :)
Marc Cousin <mcousin@sigma.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
great, thanks a lot... On Tuesday 05 May 2009 17:11:35 Tom Lane wrote: > Marc Cousin <mcousin@sigma.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