Обсуждение: Postgres connection status as BIND

Поиск
Список
Период
Сортировка

Postgres connection status as BIND

От
Nimesh Satam
Дата:
Hi,

When we do a ps U postgres command, we find some connection in BIND status:

10088 ?        Ss     0:00 postgres: chk production xxx.xx.x.xx(48672) BIND
10090 ?        Ss     0:00 postgres: chk production xxx.xx.x.xx(48674) BIND


We are connecting to the database using pgpool for load balancing and slony for replication. Postgres version - 8.3.3.

The queries running over this connections appear to be hanged and are not running.

Can anybody tell what exactly the above connection status means, and how we can get rid of this connections, pg_cancel_backend doesn't seem to help here?

Let me know I can provide some more info.

Regards,
Nimesh.

Re: Postgres connection status as BIND

От
Tom Lane
Дата:
Nimesh Satam <nimesh.zedo@gmail.com> writes:
> When we do a ps U postgres command, we find some connection in BIND status:

> 10088 ?        Ss     0:00 postgres: chk production xxx.xx.x.xx(48672) BIND
> 10090 ?        Ss     0:00 postgres: chk production xxx.xx.x.xx(48674) BIND

> Can anybody tell what exactly the above connection status means,

It's trying to do the Bind Parameters step of the prepared-query
protocol.  AFAICS, a hang here means either a problem in parameter
value conversion (have you got any custom datatypes with input functions
that maybe aren't debugged very well?) or something odd happening while
trying to plan or re-plan the query.  Have you identified just which
queries hang up this way?  (pg_stat_activity might help here.)

One fairly likely theory is that these are blocked trying to acquire
locks on tables their queries will reference.  Have you looked into
pg_locks for evidence of someone sitting on exclusive locks?

            regards, tom lane

Re: Postgres connection status as BIND

От
Alan Hodgson
Дата:
On Monday 15 June 2009, Nimesh Satam <nimesh.zedo@gmail.com> wrote:
> Hi,
>
> When we do a ps U postgres command, we find some connection in BIND
> status:
>
> 10088 ?        Ss     0:00 postgres: chk production xxx.xx.x.xx(48672)
> BIND 10090 ?        Ss     0:00 postgres: chk production
> xxx.xx.x.xx(48674) BIND
>
>
> We are connecting to the database using pgpool for load balancing and
> slony for replication. Postgres version - 8.3.3.

pgpool does that on some connections on some of my servers (but not others).
I haven't been able to figure out why.


--
WARNING:  Do not look into laser with remaining eye.