PQstatus does not seem to work

Juan Backson <juanbackson@gmail.com> writes:

I used PQstatus(conn) function to check connection status, but I found that
it still returns CONNECTION_OK even after postgres is restarted. Does
anyone know if there is another command that I can use to check connection
status?

PQstatus isn't going to get updated until you try to do some operation
with the connection object. Otherwise it wouldn't be a simple inquiry
function, but some exceedingly expensive operation involving attempting
to contact the server.

What other solution is available to check whether a connection is still
alive?

Send a query.

regards, tom lane

On Tue, Aug 11, 2009 at 12:41:47AM +0800, Juan Backson wrote:

I used PQstatus(conn) function to check connection status, but I found that
it still returns CONNECTION_OK even after postgres is restarted. Does
anyone know if there is another command that I can use to check connection
status?

Yes, PQstatus just gives back the last status. It doesn't go off and
check anything.

What other solution is available to check whether a connection is still
alive?

As a connection can go down at any time, this doesn't seem useful. Just
send off your request as normal and if it fails because the connection
was closed then you can open a new one and try again.

--
Sam http://samason.me.uk/

On 8/10/09 12:08 PM, "Sam Mason" <sam@samason.me.uk> wrote:

On Tue, Aug 11, 2009 at 12:41:47AM +0800, Juan Backson wrote:

I used PQstatus(conn) function to check connection status, but I found that
it still returns CONNECTION_OK even after postgres is restarted. Does
anyone know if there is another command that I can use to check connection
status?

Yes, PQstatus just gives back the last status. It doesn't go off and
check anything.

What other solution is available to check whether a connection is still
alive?

As a connection can go down at any time, this doesn't seem useful. Just
send off your request as normal and if it fails because the connection
was closed then you can open a new one and try again.

Depending on your situation, connection pooling might be a reasonable
option. Instead of managing the connections yourself, you leave that to
another process entirely.

http://www.revsys.com/writings/postgresql-performance.html

Look at the section on "Stateless Applications"

I spend a lot of time writing stateless apps that server many 'users'
concurrently. For me, the pooling idea is much simpler because I only
interact with the 'pool', and the pool manages opening and closing
connections on my behalf.

Of course, this is not a good option if you're writing a stateful app. Your
original email didn't say either way, so this is a take on the other side of
the problem.

Hi Tim,

Thank you for your suggestion.

In my application, it is a multi-thread and each thread will need to query 5
select statements.

Right now, I am having my own pool of 500 PgConn inside the code. For each
connection that I obtain from the connection pool, I am using direct socket
into querying the database, without ODBC. That way, I can get the data much
faster.

Does PGpool II has c api that I can use inside my code?

Also, can I use direct socket connection to query the db with PgpoolI? The
way I am executing query is by using :

res = PGexec(conn, "BEGIN");
res = PQexec(pgconn, "DECLARE CURSOR select * ....");
res = PGexec(conn, "END");

Could someone help me out? What is the best way for 1) using connectin
pooling in my situation and 2) it is the right way to do BEGIN; DECLARE
CURSOR... ; END; for each select query?

Thanks for all your help.

JB

On Tue, Aug 11, 2009 at 2:02 AM, Tim Hart <tjhart@mac.com> wrote: