Thread: PQstatus does not seem to work
Hi
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?
What other solution is available to check whether a connection is still alive?
Thanks,
JB
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:
Depending on your situation, connection pooling might be a reasonable
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.
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.