Pg: Method for testing a connection

[joelclouddistrict]

I am trying to write a piece of code that regularly checks the database connection to test if it is still open.

I can't find any method to do this, is there a way? If not, is there any way to be notified if the database connection is closed?

I think something like https://golang.org/pkg/database/sql/#DB.Ping would be useful

[vmihailenco]

Why do you need that? The purpose of connection pool is that you don't have to worry about such details...

[joelclouddistrict]

I need to notify if there is a problem with the database without having to make a regular query.

[vmihailenco]

I need to notify if there is a problem with the database

You can execute empty query or SELECT 1 to check if database is up.

without having to make a regular query.

You can use db.PoolStats() to check number of open/free connections. This is only useful if you are sure that there is constant activity.

[mxpaul]

It would be great to have ability to find out connection status without any additional queries.
A callback which is called as soon as first connection is available and another one called if last connection in pool is lost. Or maybe channel to be notified of such events.

Let's say you have HTTP API which uses postgres to process queries. This long running process need at least two things:

• Log the fact that it lost postgres connection, even if there is no user activity. Maybe send some other notification to admins.
• Probably switch to another postgres server if current one is not available. There may be even few pools with different IP address created at start time, and when application receives request, it choose pools currently available for query. It is very slow to send "select 1" to all servers to know if they are alive, especially in case of network connectivity lost. Instead, application should be able to skip trying sending any requests unless it is really connected to postgres server.

[soupdiver]

Why do you need that? The purpose of connection pool is that you don't have to worry about such details...

My usecase is to check if credentials are ok because Connect does not return an err

[kogisin]

@soupdiver Same usecase here.

[soupdiver]

@vmihailenco Are you strictly against this feature or would you be open for a PR? It seems there is at least some interest around this.

[vmihailenco]

It is not obvious what do you mean specifically. Also what is wrong with SELECT 1? We could hide it under Ping name but why?

[soupdiver]

It is not obvious what do you mean specifically

My usecase is to check if credentials are ok because Connect does not return an err

This is my personal use case. If you provide wrong credentials there should be an error I guess or as couple of other people mentioned there are multiple scenarios in which it makes sense to check if the database connection is (still) intact. IMHO this is a fundamental thing of a driver to tell if the connection is ok. Why should everyone re-implement this?

[vmihailenco]

Re-implement what? db.Exec("SELECT 1")?

[soupdiver]

Re-implement what? db.Exec("SELECT 1")?

Ok, fair enough... it's your project I don't need argue if you don't want to understand.

[vmihailenco]

If your point is that people are looking for db.Ping and we should provide it just to save them the time to google the answer to use db.Exec("SELECT 1") - it makes sense. If you willing to write the code and tests - please send PR.

My point for not providing it already is that Postgres protocol does not support such primitive and wrapping Exec does not add any value. Also I am having hard time imagining what people can do with the result. This comment explains some possibilities but I believe there are better/simpler way to achieve the same.

[soupdiver]

If your point is that people are looking for db.Ping and we should provide it just to save them the time to google the answer to use db.Exec("SELECT 1") - it makes sense. If you willing to write the code and tests - please send PR.

Yes, this is basically my point. Quite a few people invested the time to google for this features, open PR and engagen in discussions. As said _imho_ this is something fundamental a driver should provide and it would save all these people some amount of time.

Also I am having hard time imagining what people can do with the result.

A couple of people explained their scenarios... I though that would help to understand the beenfit of it.

[kogisin]

From my experience, I naturally thought that Connect would return an err if database connection fails due to incorrect credentials. Once I realized it is not, then I looked for some other mehod like Ping to check database connection. It would nice to implement a feature to check database connection other than using the query db.Exec("SELECT 1"). I think it would save time for people like me.

[nghtstr]

I have to agree with @kogisin about this. It is just good practice to have Connect return an error if there is one. It is used extensively everywhere else, so I just fail to see why it wouldn't be there during the initial Connect function. Also, this is entirely different from a Ping, which tests to see if the connection is alive (and I agree, that is really unnecessary).

[vmihailenco]

The problem is that pg.Connect does not open any connections. Ping may open a new connection or may use an existing one from the pool.

[nghtstr]

My question is if you do any sort of sanity checking when the connection information is passed to the Connect() function? I would assume that you’d have to do something, and if it fails, that is when you can pass back the error response.