1

According to the manual ( https://php.net/pg_ping ):

pg_ping() pings a database connection and tries to reconnect it if it is broken.

Aaaalright... So where would one ever do that? Is this for many-hours-long while (1) loops which only very rarely makes a query? How long is the timeout normally? Why is there a timeout at all? Should I change some setting? I don't understand the purpose of this feature/function. I can't find any sensible example or explanation as to when it would ever be used, or why.

Please explain.

  • _"So where would one ever do that?"_ - It's for when you need to make sure you have a DB-connection before you try and use it? _"Why is there a timeout at all?"_ - If you didn't have a timeout, you would end up with a lot of hanging connections. A timeout makes sure that there won't be a bunch of those. Also, if you don't understand the purpose of a function, you've probably just not needed it yet. There's a _lot_ of functions I've never needed. – M. Eriksson Jun 03 '19 at 04:47

1 Answers1

0

First, a factual answer:

The function is there to test if the connection is still active. The intention is to test a connection that could have been idle for a while, for example one that you just grabbed from a connection pool.

The intention is to minimize the risk of failure during an immediately following database request.

An opinionated answer:

This concept is ill-advised, and your feeling that there is something wrong with it is justified.

There are two problems:

  1. A race condition. Even if it ia not likely, the connection can still go bad between the time you test it and the time you use it.

    So if you want to write robust code, you still have to make provisions for connection failure in the cide that uses the database connection (retry, not let the error propagate to the user immediately, ...)

  2. In the likely case that the connection is still fine, you incur the unnecessary cost of a client-server round trip, a cost that you will have to pay for every database request.

    Therefore, it is better to follow the principle of “try first, apologize later”: don't uae functions like this. Rather, proceed with the database request you actually intend to do, catch any errors, and if there are errors that indicate a broken connection, re-establish it and retry. Your code will become simpler and more robust.

I wouldn't set any timeouts on database connections at all. Rather, use a connection pool that manages that for you. It will keep some connections around in anticipation of database activity and close extra connections after a certain idle time.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Using pg_ping before sending traffic on a connection that's been idle for >90s improves the error _timing_ dramatically in a significant error case, namely NAT middleboxes dropping packets. I've implemented it in my server connection pooling code, and now have reliable subsecond NAT-deadness detection, with no measurable performance cost to the non-error cases (the pings are streamed). – arnt Jun 07 '19 at 15:17