 Hi World! 

 Does backend О©╫reates pid file?

  Postgres 6.5 backend (current CVS) stop answering query

 >>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed: PQsendQuery()
 >>> There is no connection to the backend. 

 every 300 000 cursor allocation so I wish to restart it every 100 000
 is there a way to do it simple than 
   kill  `ps ax | awk .....`

 Thank you!

PS:
  6.4 works without problems with the same code, but I need more flexible
locking on our new mail server.


---
Dmitry Samersoff, dms@wplus.net, ICQ:3161705
http://devnull.wplus.net
* There will come soft rains ...

Wed, 02 Jun 1999 18:56:55 +0400 (MSD)
Dmitry Samersoff <dms@wplus.net>
PID of backend
On Wed, 2 Jun 1999, Dmitry Samersoff wrote:

> Date: Wed, 02 Jun 1999 18:56:55 +0400 (MSD)
> From: Dmitry Samersoff <dms@wplus.net>
> To: pgsql-hackers@postgreSQL.org
> Subject: [HACKERS] PID of backend
> 
>  Hi World! 
> 
>  Does backend О©╫reates pid file?
> 
>   Postgres 6.5 backend (current CVS) stop answering query
> 
>  >>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed: PQsendQuery()
>  >>> There is no connection to the backend. 
> 
>  every 300 000 cursor allocation so I wish to restart it every 100 000
>  is there a way to do it simple than 
>    kill  `ps ax | awk .....`

Have you tried pidof postmaster ?
kill `pidof postmaster` | ....


	Oleg

> 
>  Thank you!
> 
> PS:
>   6.4 works without problems with the same code, but I need more flexible
> locking on our new mail server.
> 
> 
> ---
> Dmitry Samersoff, dms@wplus.net, ICQ:3161705
> http://devnull.wplus.net
> * There will come soft rains ...
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Wed, 2 Jun 1999 19:05:05 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] PID of backend

On 02-Jun-99 Oleg Bartunov wrote:
> On Wed, 2 Jun 1999, Dmitry Samersoff wrote:
> 
>> Date: Wed, 02 Jun 1999 18:56:55 +0400 (MSD)
>> From: Dmitry Samersoff <dms@wplus.net>
>> To: pgsql-hackers@postgreSQL.org
>> Subject: [HACKERS] PID of backend
>> 
>>  Hi World! 
>> 
>>  Does backend О©╫reates pid file?
>> 
>>   Postgres 6.5 backend (current CVS) stop answering query
>> 
>>  >>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed:
>>  >>> PQsendQuery()
>>  >>> There is no connection to the backend. 
>> 
>>  every 300 000 cursor allocation so I wish to restart it every 100 000
>>  is there a way to do it simple than 
>>    kill  `ps ax | awk .....`
> 
> Have you tried pidof postmaster ?
> kill `pidof postmaster` | ....

  I have no pidof on my computer but it's exactly the same solution.
I need something usable inside program other than scanning process table.

 The best one -  add code to create postgres.5432.pid 
to backend startup.

It also is good way to automatically unlink socket file after 
backend crush.
  (read pidfile, check existence of process and remove file if no process
found)
    


---
Dmitry Samersoff, dms@wplus.net, ICQ:3161705
http://devnull.wplus.net
* There will come soft rains ...

Wed, 02 Jun 1999 19:52:43 +0400 (MSD)
Dmitry Samersoff <dms@wplus.net>
Re: [HACKERS] PID of backend
>>> Postgres 6.5 backend (current CVS) stop answering query
>>> 
>>>>>>>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed:
>>>>>>>> PQsendQuery()
>>>>>>>> There is no connection to the backend. 
>>> 
>>> every 300 000 cursor allocation so I wish to restart it every 100 000
>>> is there a way to do it simple than 
>>> kill  `ps ax | awk .....`

Why in the world do you want to use kill at all?  If you want to get
rid of your current backend, just close the connection.  I really doubt
that killing the postmaster is necessary or appropriate.

(Of course the real answer is to find a way to avoid the memory leak that
I suppose you are running into.  But you haven't given us enough info
to offer any advice in that direction.)

> I need something usable inside program other than scanning process table.

There is a libpq function that will tell you the PID of the currently
connected backend: PQbackendPID.  But it's not usually good for much
except debugging purposes.

			regards, tom lane

Wed, 02 Jun 1999 13:32:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] PID of backend 

On 02-Jun-99 Tom Lane wrote:
>>>> Postgres 6.5 backend (current CVS) stop answering query
>>>> 
>>>>>>>>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed:
>>>>>>>>> PQsendQuery()
>>>>>>>>> There is no connection to the backend. 
>>>> 
>>>> every 300 000 cursor allocation so I wish to restart it every 100 000
>>>> is there a way to do it simple than 
>>>> kill  `ps ax | awk .....`
> 
> Why in the world do you want to use kill at all?  If you want to get
> rid of your current backend, just close the connection.  I really doubt
> that killing the postmaster is necessary or appropriate.
> 
> (Of course the real answer is to find a way to avoid the memory leak that
> I suppose you are running into.  But you haven't given us enough info
> to offer any advice in that direction.)
> 
>> I need something usable inside program other than scanning process table.
> 
> There is a libpq function that will tell you the PID of the currently
> connected backend: PQbackendPID.  But it's not usually good for much
> except debugging purposes.

Thanks ! It's exactly what I need. (But it s'seems not documented ?) 

I need to restart backend because (as written  above) 
every 300 000 "open cursor" query completly loose it's mind.



---
Dmitry Samersoff, dms@wplus.net, ICQ:3161705
http://devnull.wplus.net
* There will come soft rains ...

Wed, 02 Jun 1999 22:09:24 +0400 (MSD)
Dmitry Samersoff <dms@wplus.net>
Re: [HACKERS] PID of backend
[Charset KOI8-R unsupported, filtering to ASCII...]
> 
> On 02-Jun-99 Tom Lane wrote:
> >>>> Postgres 6.5 backend (current CVS) stop answering query
> >>>> 
> >>>>>>>>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed:
> >>>>>>>>> PQsendQuery()
> >>>>>>>>> There is no connection to the backend. 
> >>>> 
> >>>> every 300 000 cursor allocation so I wish to restart it every 100 000
> >>>> is there a way to do it simple than 
> >>>> kill  `ps ax | awk .....`
> > 
> > Why in the world do you want to use kill at all?  If you want to get
> > rid of your current backend, just close the connection.  I really doubt
> > that killing the postmaster is necessary or appropriate.
> > 
> > (Of course the real answer is to find a way to avoid the memory leak that
> > I suppose you are running into.  But you haven't given us enough info
> > to offer any advice in that direction.)
> > 
> >> I need something usable inside program other than scanning process table.
> > 
> > There is a libpq function that will tell you the PID of the currently
> > connected backend: PQbackendPID.  But it's not usually good for much
> > except debugging purposes.
> 
> Thanks ! It's exactly what I need. (But it s'seems not documented ?) 
> 
> I need to restart backend because (as written  above) 
> every 300 000 "open cursor" query completly loose it's mind.

I hope 6.5 due on June 7 will fix your problems.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 2 Jun 1999 14:14:55 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] PID of backend
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> I need to restart backend because (as written  above) 
>> every 300 000 "open cursor" query completly loose it's mind.

> I hope 6.5 due on June 7 will fix your problems.

It might.  Looking back at the original gripe, I notice it mentions
doing rollbacks:

>>>>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed:

If Dmitry is doing a whole lot of rollbacks, he might be running into
that aborted-transactions-leak-memory bug that we fixed a few weeks ago.

Meanwhile, I still say that getting rid of a backend via kill() is a
dangerous and unnecessary "recovery" mechanism.  What's wrong with
just closing and reopening the connection instead?

			regards, tom lane

Wed, 02 Jun 1999 15:14:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] PID of backend 
On Wed, 2 Jun 1999, Dmitry Samersoff wrote:

> Thanks ! It's exactly what I need. (But it s'seems not documented ?) 
> 
> I need to restart backend because (as written  above) 
> every 300 000 "open cursor" query completly loose it's mind.

I'm lost here...doesn't doing a close and then reopening the connection
restart the backend?  Why would you have to 'kill' it?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 3 Jun 1999 01:15:50 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] PID of backend

On 03-Jun-99 The Hermit Hacker wrote:
> On Wed, 2 Jun 1999, Dmitry Samersoff wrote:
> 
>> Thanks ! It's exactly what I need. (But it s'seems not documented ?) 
>> 
>> I need to restart backend because (as written  above) 
>> every 300 000 "open cursor" query completly loose it's mind.
> 
> I'm lost here...doesn't doing a close and then reopening the connection
> restart the backend?  Why would you have to 'kill' it?

After 300 000 open cursor query (and certtainly close cursor), 
next query returns error like example below for all other connections 
(ROLLBACK is a query from other process, it just example of message nothing
more)

 - ie backend exists in memory but stops answer query and allocates new
connections. 

>> Jun  2 00:12:32 mail popper[17585]: PgSQL:ROLLBACK failed: PQsendQuery()
>>> There is no connection to the backend. 

---
Dmitry Samersoff, dms@wplus.net, ICQ:3161705
http://devnull.wplus.net
* There will come soft rains ...

Thu, 03 Jun 1999 16:23:01 +0400 (MSD)
Dmitry Samersoff <dms@wplus.net>
Re: [HACKERS] PID of backend


Tom Lane wrote:
> 
> Meanwhile, I still say that getting rid of a backend via kill() is a
> dangerous and unnecessary "recovery" mechanism.  What's wrong with
> just closing and reopening the connection instead?

I don't know about later versions of pgsql, but I've a 6.3.2 system running
on a production system, and every once in a while one of the
backends will go crazy and eat CPU. This system is on a web server, and processes
requests tru a CGI script. For the administrator (i.e. me), it is impossible
to close the CGI<-->backend connection (the backend will keep running after I kill
off the CGI script). Only thing that will get things back in order is to kill that
backend (which sometimes also requires me to restart the postmaster, probably
because of some shared mem corruption).

Maarten

ps. This system is not a priority for me, I', quote happy with how it's running,
so please don't tell me to upgrade or give me any other suggestions.

-- 

Maarten Boekhold, boekhold@tibco.com
TIBCO Finance Technology Inc.
The Atrium
Strawinskylaan 3051
1077 ZX Amsterdam, The Netherlands
tel: +31 20 3012158, fax: +31 20 3012358
http://www.tibco.com

Tue, 08 Jun 1999 15:49:22 +0200
Maarten Boekhold <boekhold@tibco.com>
Re: [HACKERS] PID of backend