Обсуждение: what to do when pg_cancel_backend() doesnt work?

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?

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=#

On 6/4/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> 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)
>

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)