[SQL] " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

2011-01-04 Thread Bryce Nesbitt 

Dear helpful postgres people;

I've got a database deadlock.  The initiating process was " in 
transaction" with PID 2219.
Use of pg_cancel_backend() returns true, but does not actually kill the 
process.


When this happens I kill the PID at the Unix level, which feels sort of 
stupid, like driving on the hood

of a car on the freeway without a safety harness.

Should I expect pg_cancel_backend() to work in a case like this?  If not 
does this sound like a reportable bug?


Also: is there a way I can get the value of $1 in the select for update 
shown below?


-
# select version();
PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 
20061115 (prerelease) (Debian 4.1.1-21)


# select procpid,now()-query_start,usename,substr(current_query,0,1000) 
from pg_stat_activity order by 2 desc;


 procpid |?column? |  usename   |
-+-++---
   32507 | 22:31:09.736599 | jira   | 
2219 | 04:01:59.139841 | production |  in transaction
3428 | 03:58:06.149227 | production | select object,utf8 
from user_objects where userid = $1 for update
6574 | 03:46:35.627382 | production | select object,utf8 
from user_objects where userid = $1 for update

4456 | 02:41:33.680257 | production | 
   26217 | 02:26:49.609589 | production | select object,utf8 
from user_objects where userid = $1 for update
   29562 | 02:17:00.434927 | production | select object,utf8 
from user_objects where userid = $1 for update
2123 | 00:28:04.877832 | production | select object,utf8 
from user_objects where userid = $1 for update



=# SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks 
l,pg_class c where l.relation=c.oid order by relname,granted;


 locktype |   relname   |  pid  |   
mode   | granted

--+-+---+--+-
 relation | context_keyvals |  5841 | 
RowExclusiveLock | t
 relation | context_keyvals_ckey|  5841 | 
RowExclusiveLock | t
 relation | context_objects |  2219 | 
RowShareLock | t
 relation | context_objects_pkey|  2219 | 
AccessShareLock  | t
 relation | contexts|  2549 | 
AccessShareLock  | t
 relation | contexts|  5841 | 
RowShareLock | t
 relation | contexts_id_key |  5841 | 
AccessShareLock  | t
 relation | contexts_pkey   |  5841 | 
AccessShareLock  | t
 relation | contexts_publication_date_idx   |  5841 | 
AccessShareLock  | t
 relation | contexts_site_key_ct_id_journal_key |  5841 | 
AccessShareLock  | t
 relation | contexts_site_key_ct_type_idx   |  5841 | 
AccessShareLock  | t
 relation | pg_class|  3738 | 
AccessShareLock  | t
 relation | pg_class_oid_index  |  3738 | 
AccessShareLock  | t
 relation | pg_class_relname_nsp_index  |  3738 | 
AccessShareLock  | t
 relation | pg_locks|  3738 | 
AccessShareLock  | t
 relation | pg_toast_3794455612 | 10155 | 
AccessShareLock  | t
 relation | pg_toast_3794455612_index   | 10155 | 
AccessShareLock  | t
 tuple| user_objects| 26217 | 
ExclusiveLock| f
 tuple| user_objects|  6574 | 
ExclusiveLock| f
 tuple| user_objects|  2123 | 
ExclusiveLock| f
 tuple| user_objects| 29562 | 
ExclusiveLock| f
 relation | user_objects|  2123 | 
RowShareLock | t
 relation | user_objects| 26217 | 
RowShareLock | t
 relation | user_objects| 10155 | 
AccessShareLock  | t
 tuple| user_objects|  3428 | 
ExclusiveLock| t
 relation | user_objects|  3428 | 
RowShareLock | t
 relation | user_objects|  6574 | 
RowShareLock | t
 relation | user_objects|  2219 | 
RowShareLock | t
 relation | user_objects| 29562 | 
RowShareLock | t
 relation | user_objects_pkey   |  6574 | 
AccessShareLock  | t
 relation | user_objects_pkey   |  2123 | 
AccessShareLock  | t
 relation | user_objects_pkey   | 26217 | 
AccessShareLock  | t
 relation | user_objects_pkey   |  3428 | 
AccessShareLock  | t
 relation | user_objects_pkey   | 29562 | 
AccessShareLock  | t
 relation | user_objects_pkey   |  2219 | 
AccessShareLock  | t
 relation | user_objects_pkey

Re: [SQL] " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

2011-01-04 Thread Bryce Nesbitt 

Michael Glaesemann wrote:

On Jan 4, 2011, at 17:29, Bryce Nesbitt wrote:
   

Should I expect pg_cancel_backend() to work in a case like this?  If not does 
this sound like a reportable bug?
 

pg_cancel_backend() cancels the running query for that backend. In the case 
of, there is no running query for that backend.

Likely you're looking for pg_terminate_backend().
Michael Glaesemann
grzm seespotcode net
   
Hmm, yes.  Though it seems to not be in the documentation, or, for that 
matter, the current code:


utils/adt/misc.c

#*ifdef* NOT_USED

//* Disabled in 8.0 due to reliability concerns; FIXME someday *//
*pg_terminate_backend*(PG_FUNCTION_ARGS




# select pg_terminate_backend(5);
ERROR:  function pg_terminate_backend(integer) does not exist
LINE 1: select pg_terminate_backend(5);
   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.



--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

2011-01-04 Thread Michael Glaesemann 

On Jan 4, 2011, at 17:29, Bryce Nesbitt wrote:

> Should I expect pg_cancel_backend() to work in a case like this?  If not does 
> this sound like a reportable bug?

pg_cancel_backend() cancels the running query for that backend. In the case of 
, there is no running query for that backend.

Likely you're looking for pg_terminate_backend().

Michael Glaesemann
grzm seespotcode net




-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

2011-01-04 Thread bricklen 
On Tue, Jan 4, 2011 at 2:47 PM, Bryce Nesbitt  wrote:
> Michael Glaesemann wrote:
>> Likely you're looking for pg_terminate_backend().
>> Michael Glaesemann
>> grzm seespotcode net
>>
>
> Hmm, yes.  Though it seems to not be in the documentation, or, for that
> matter, the current code:

8.4+

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql