Re: connection watchdog

Hi Postgres gurus!

How can I check from C-program whether connection to pgsql
engine still works or already lost?
PQstatus() returns CONNECTION_OK even when backend process is killed
by hands after connection is made.

ps: Searching through pgsql-mail-lists archive and Faq gave nothing.

thanx in advance,
mike

On 10 Oct 2002 at 18:45, Michael Kichanov wrote:

> Hi Postgres gurus!
>
> How can I check from C-program whether connection to pgsql
> engine still works or already lost?
> PQstatus() returns CONNECTION_OK even when backend process is killed
> by hands after connection is made.

There is a function which returns fd of the connection to postgresql database.
If you install a SIGPIPE handler on that connections, perhaps you can catch the
signal that backend is killed. But you will get that signal only when you
attempt to write to the fd, according to 'man 7 signal' on my mandrake box.

Just theory. Try it out and let us know if it works..

HTH

Bye
Shridhar

--
Preudhomme's Law of Window Cleaning: It's on the other side.

Michael Kichanov wrote:
> Hi Postgres gurus!
>
> How can I check from C-program whether connection to pgsql
> engine still works or already lost?
> PQstatus() returns CONNECTION_OK even when backend process is killed
> by hands after connection is made.

> ps: Searching through pgsql-mail-lists archive and Faq gave nothing.

I think you can't know if a connection is ok before trying to send
a query to the server (IMHO)

If you really want to know before, you can send a simple query (select '1')
and test the PGResult
(!= PGRES_COMMAND_OK or !=PGRES_TUPLES_OK or ==PGRES_BAD_RESPONSE or ==PGRES_FATAL_ERROR)

> > How can I check from C-program whether connection to pgsql
> > engine still works or already lost?
> > PQstatus() returns CONNECTION_OK even when backend process is killed
> > by hands after connection is made.
>
> There is a function which returns fd of the connection to postgresql database.
> If you install a SIGPIPE handler on that connections, perhaps you can catch the
> signal that backend is killed. But you will get that signal only when you
> attempt to write to the fd, according to 'man 7 signal' on my mandrake box.
>
> Just theory. Try it out and let us know if it works..

The point is that I don't want to use an interface level lower
than libpq interface level. At present I using a common "select 1" query
(via PQexec()) with check it reslut for this purpose. But this method
results in growing pgsql log and needless load of PG engine.

> Bye
> Shridhar

mike

On 10 Oct 2002 at 19:28, Michael Kichanov wrote:

> > > How can I check from C-program whether connection to pgsql
> > > engine still works or already lost?
> > > PQstatus() returns CONNECTION_OK even when backend process is killed
> > > by hands after connection is made.
> >
> > There is a function which returns fd of the connection to postgresql database.
> > If you install a SIGPIPE handler on that connections, perhaps you can catch the
> > signal that backend is killed. But you will get that signal only when you
> > attempt to write to the fd, according to 'man 7 signal' on my mandrake box.
> >
> > Just theory. Try it out and let us know if it works..
>
> The point is that I don't want to use an interface level lower
> than libpq interface level. At present I using a common "select 1" query
> (via PQexec()) with check it reslut for this purpose. But this method
> results in growing pgsql log and needless load of PG engine.

Well, I don't see any other way out. So if you want it, you should try this
out. Or dig aroung libpq source code to check what exact error it would throw
if the server side socket is closed.

HTH

Bye
Shridhar

--
He's dead, Jim. -- McCoy, "The Devil in the Dark", stardate 3196.1

> > How can I check from C-program whether connection to pgsql
> > engine still works or already lost?
> > PQstatus() returns CONNECTION_OK even when backend process is killed
> > by hands after connection is made.
>
> > ps: Searching through pgsql-mail-lists archive and Faq gave nothing.
>
> I think you can't know if a connection is ok before trying to send
> a query to the server (IMHO)
>
> If you really want to know before, you can send a simple query (select '1')
> and test the PGResult
> (!= PGRES_COMMAND_OK or !=PGRES_TUPLES_OK or ==PGRES_BAD_RESPONSE or ==PGRES_FATAL_ERROR)

thanx, but now I doing just this thing :)
and I would need something like PQping() ;)

mike

Michael Kichanov <mike(at)nordlink(dot)ru> wrote:
>
> thanx, but now I doing just this thing :)
> and I would need something like PQping() ;)
>
> mike
>

In our listen/notify cache i just collect the socket and select() on it.
When it returns we do a PQconsumeInput(conn); i think that catches a
backend being killed for example.

Magnus

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Programmer/Networker [|] Magnus Naeslund
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-