Обсуждение: Detecting backend failures via libpq / DBD::Pg
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 I am working on enhancing the ping() method of DBD::Pg. The goal of that is for a user to be able to determine if the connection to the database is still valid. The basic way we do this is to send a simple SELECT via PQexec and then check for a valid return value (and when in doubt, we check PQstatus). This works fine for most transaction statuses, including idle, active, and idle in transaction. It even works for copy in and copy out, although it obviously invalidates the current COPY (caveat emptor!). The problem comes when ping() is called and we are in a failed transaction. After some experimenting, the best solution I found is to send the PQexec, and then check if PQresultErrorField(result, 'C') is '25P02'. If it is, then all is "well", in that the server is still alive. If it is not, then we can assume the backend is bad (for example, PQerrorMessage gives a "could not receive data from server: Bad file descriptor"). Being that we cannot do a rollback before calling the PQexec, is this a decent solution? Can we depend on really serious errors always trumping the expected 25P02? - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201412291942 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlSh9QEACgkQvJuQZxSWSsjDMQCg3CO1eyrFXNUnfRbk/rRJmrCl PEoAnRl+M67kTkuZDi+3zMyVyblLvl9I =uW6Q -----END PGP SIGNATURE-----
>>>>> "Greg" == Greg Sabino Mullane <greg@turnstep.com> writes: Greg> I am working on enhancing the ping() method of DBD::Pg. TheGreg> goal of that is for a user to be able to determineif theGreg> connection to the database is still valid. The basic way we doGreg> this is to send a simple SELECTvia PQexec Why not PQexec(conn, "") ? -- Andrew (irc:RhodiumToad)
On 12/29/14, 6:43 PM, Greg Sabino Mullane wrote: > I am working on enhancing the ping() method of DBD::Pg. The goal of that > is for a user to be able to determine if the connection to the database > is still valid. This is actually a VERY common thing for monitoring frameworks to do. Currently, the general method seems to be somethingakin to psql -c 'SELECT 1' ... perhaps instead of going through all the effort that you currently are we could add a FEBE ping command, and expose thatthrough a special psql option (or maybe a special pg_ping command). This won't be noticeably faster than SELECT 1, but it would prevent a bunch of pointless work on the backend side, and shouldgreatly simplify DBD's ping(). Only thing I'm not sure of is if this could be made to be safe within a COPY... :( -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Andrew Gierth asked: >> this is to send a simple SELECT via PQexec > > Why not PQexec(conn, "") ? Because I want to leave a good clue for debugging; so DBAs are better able to figure out where a mystery slew of queries is coming from. The query is: SELECT 'DBD::Pg ping test' Which also means the inverse is true: simple blank queries are guaranteed to *not* be coming from DBD::Pg. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201412301041 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlSix/YACgkQvJuQZxSWSsjILwCdHnkhYC1i+LJZkNUWjfTi5yG+ FHwAn007+arJIw62gIUO20+SxnzRT4ub =9Rym -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 12/30/2014 08:43 AM, Greg Sabino Mullane wrote: > I am working on enhancing the ping() method of DBD::Pg. The goal of > that is for a user to be able to determine if the connection to the > database is still valid. This entire concept is flawed. IMO the documentation for it should be marked with a warning pointing out that use of this method to validate a connection before use implies a race condition; it is never safe to "validate" a connection then assume it'll actually be valid and working for the following statement(s). Correctly written applications must instead tolerate failures, trapping errors/exceptions at each statement and retrying the transaction. > The basic way we do this is to send a simple SELECT via PQexec and > then check for a valid return value (and when in doubt, we check > PQstatus). At the protocol level all that's needed, and all that should be done IMO, is to send a Sync message and wait for the server's ReadyForQuery response. It might be nice to expose a libpq function to do this. It's only safe to do this when there's no in-progress query, but that's true of *any* validation method you might choose. > After some experimenting, the best solution I found is to send the > PQexec, and then check if PQresultErrorField(result, 'C') is > '25P02'. If it is, then all is "well", in that the server is still > alive. If it is not, then we can assume the backend is bad (for > example, PQerrorMessage gives a "could not receive data from > server: Bad file descriptor"). Being that we cannot do a rollback > before calling the PQexec, is this a decent solution? Can we depend > on really serious errors always trumping the expected 25P02? I struggle to understand the utility of this. Your transaction could be in the failed state because of an internal error caused by the server's storage failing. So it's "alive" in the sense of responding to queries, but completely useless. This is another aspect of why the whole approach is wrong. If you want to find out if the server can run a particular query, run the query. If you get an error, then it couldn't run that query. - -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQEcBAEBAgAGBQJUp1IhAAoJELBXNkqjr+S2hvwH/3V1jNIYTzjVKlMKGfiOmXAH FAdy1PznxZx/DWidcIl7AfSa+9SDoF+cqR9leH1ju0MCy1VkS0W/Lx9lFEfDm6fU s75kN0zno0N9wbSb/sMLGCFEv4wyX3On0rC401NY7/2HXDWco227JfH7O0fAz/lv dNUDdmIg2+d0J1lKyTQ9Z5T8hl8SvMuRvnoaT0/5/6sqSRL3S/hSE0pObFGpKG0I hWpklz3nQwMXZgLOt1YmSAprBd6HyUIzQDG0mV8QQ4SKn7M92J5KSgN1ORyVbMGZ ImKJ+EpnUVEA+n/yG/CV/u27OfKVSYVQZJLZE3XepLY+/eBI3Ai2d+wK7x9Yrfk= =NkUj -----END PGP SIGNATURE-----