what to do when pg_cancel_backend() doesnt work?

Rhys Stewart escribi�:

Well,
that pretty much sums it up. pg_cancel_backend() is not working. The
query is still there. The box is across the city and the admin is not
in, is there a way to remote restart the server from within PG?

It is probably a bug and if you gives us some information we might be
able to fix it. For example what is it doing. And what version it is.

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda ense�ar algo." (Jean B. Say)

well there is the info below:

GISDEV=# select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)
(1 row)

GISDEV=# select * from pg_catalog.pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query | query_start |
backend_start | client_addr | client_port
-------+----------+---------+----------+----------+------------------------------+----------------------------+----------------------------+----------------+-------------
19015 | GISDEV | 3584 | 17024 | gisadmin | <IDLE>
| 2007-06-04 10:04:46.172-04 | 2007-06-04 09:33:18.399-04 |
130.100.30.124 | 3491
19015 | GISDEV | 2460 | 17024 | gisadmin | <IDLE>
| 2007-06-04 09:34:33.07-04 | 2007-06-04 09:34:33.07-04 |
130.100.30.124 | 3493
19015 | GISDEV | 4156 | 17024 | gisadmin | <IDLE>
| 2007-06-04 10:03:40.267-04 | 2007-06-04 09:57:56.005-04 |
130.100.30.124 | 3589
19015 | GISDEV | 2960 | 17024 | gisadmin | <IDLE>
| 2007-06-04 09:34:26.398-04 | 2007-06-01 14:49:20.534-04 |
130.100.30.124 | 2874
19015 | GISDEV | 3288 | 17024 | gisadmin | drop table tmp.kpsall3buff ;
create table tmp.kpsall3buff with oids as
select pole_id,string,filename,len,buffer(geo,32.5) from tmp.jpsall3

--limit 50 | 2007-06-01 10:20:45.969-04 | 2007-06-01 10:12:51.472-04 |
130.100.30.124 | 2130
10793 | postgres | 392 | 17024 | gisadmin | <IDLE>
| 2007-06-04 09:33:18.837-04 | 2007-06-04 09:33:18.134-04 |
130.100.30.124 | 3490
19015 | GISDEV | 1860 | 10 | postgres | <IDLE>
| 2007-06-04 11:07:12.874-04 | 2007-06-04 10:31:18.089-04 |
130.100.30.124 | 3666
19015 | GISDEV | 5216 | 10 | postgres | <IDLE>
| 2007-06-04 11:07:12.843-04 | 2007-06-04 10:34:38.977-04 |
130.100.30.124 | 3720
19015 | GISDEV | 5024 | 10 | postgres | <IDLE>
| 2007-06-04 11:08:50.685-04 | 2007-06-01 17:36:36.707-04 |
130.100.30.124 | 3057
(9 rows)

GISDEV=# select * from pg_catalog.pg_locks ;
locktype | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid | mode
| granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+---------------------+---------
relation | 19015 | 5220813 | | | |
| | | 15820036 | 3288 | AccessExclusiveLock |
t
relation | 19015 | 5223128 | | | |
| | | 15820036 | 3288 | ShareLock |
t
relation | 19015 | 5223126 | | | |
| | | 15820036 | 3288 | AccessExclusiveLock |
t
relation | 19015 | 5223130 | | | |
| | | 15820036 | 3288 | ShareLock |
t
relation | 19015 | 5223130 | | | |
| | | 15820036 | 3288 | AccessExclusiveLock |
t
transactionid | | | | | 15822393 |
| | | 15822393 | 3564 | ExclusiveLock |
t
relation | 19015 | 5220811 | | | |
| | | 15820036 | 3288 | AccessExclusiveLock |
t
object | 0 | | | | |
1260 | 17024 | 0 | 15820036 | 3288 | AccessShareLock |
t
relation | 19015 | 5220811 | | | |
| | | 15822393 | 3564 | AccessShareLock |
f
transactionid | | | | | 15820036 |
| | | 15820036 | 3288 | ExclusiveLock |
t
transactionid | | | | | 15844904 |
| | | 15844904 | 5024 | ExclusiveLock |
t
relation | 19015 | 10342 | | | |
| | | 15844904 | 5024 | AccessShareLock |
t
relation | 19015 | 5220815 | | | |
| | | 15820036 | 3288 | AccessExclusiveLock |
t
relation | 19015 | 3781129 | | | |
| | | 15820036 | 3288 | AccessShareLock |
t
(14 rows)

GISDEV=#

a more readable version

Rhys Stewart escribi�:

a more readable version

What is this buffer() function?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Jun 04, 2007 at 12:00:10PM -0400, Alvaro Herrera wrote:

Rhys Stewart escribi�:

a more readable version

What is this buffer() function?

Looks like the PostGIS buffer() function, which calls GEOSBuffer()
in the GEOS library, which is where the code might be stuck.

http://postgis.refractions.net/docs/ch06.html#id2527029
http://geos.refractions.net/

--
Michael Fuhr

Michael Fuhr escribi�:

On Mon, Jun 04, 2007 at 12:00:10PM -0400, Alvaro Herrera wrote:

Rhys Stewart escribi�:

a more readable version

What is this buffer() function?

Looks like the PostGIS buffer() function, which calls GEOSBuffer()
in the GEOS library, which is where the code might be stuck.

http://postgis.refractions.net/docs/ch06.html#id2527029
http://geos.refractions.net/

Yeah, that's what Rhys told me by private email. I suggested asking the
PostGIS guys, but if GEOS is intended to be a platform-neutral module, I
guess it's not very likely that they'll be adding a CHECK_FOR_INTERRUPTS
in the loops there.

(Peeking much further starts to get too time-consuming for me so I left
it at that.)

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"El destino baraja y nosotros jugamos" (A. Schopenhauer)