Loosing postgres (TPQConnection) due to local dynamic IP address change

[MarkMLl]

I raised this on Stack Exchange and didn't get a definite answer: only two suggestions to use ss (unix Socket Status command) to check the state. Interestingly, one of those came from somebody who I know to be very much a PostgreSQL insider with many years of experience.

I've updated the file I posted earlier, and also created https://github.com/MarkMLl/testdb As well as what it did earlier, the test program now extracts details of the socket from the Postgres client state, and uses the ident protocol (port 113) to query the server OS (i.e. without going anywhere near the Postgres server daemons) as to whether the connection/session is still active.

If the server process associated with a session times out (e.g. because the client is running on a laptop which has gone to sleep state) or is aborted (client IP address change, after some indeterminate period) or if the server is quite simply unreachable (network failure etc.) the app's status will indicate this, thus alerting the user etc. that an attempt to use the database will fail, even if from the POV of DB-aware components there is still a grid etc. which appears to be active (and will accept edits until a transaction is forced, at which point work will be lost).

There's problems related to host lookup in my demonstration code: it's being done too often, the IP address should either be recovered from the Postgres client state or the original server address should be cached; it would probably also be useful to cache the original client address and to check with the OS that it was unchanged on a regular basis (this is non-trivial so will be left as an exercise).

I've avoided using ICMP echo request ("ping") since it would require that the binary be blessed with elevated capabilities.

So, I feel that what I've got there is a useful proof-of-concept.

However, this takes me onto an issue that I discovered while sniffing around the Postgres connection state. In postgres3types.inc there is a declaration of TPGconn which is grossly out of date. This is documented by the PostgreSQL project as being internal to libpq and it has to be assumed unstable, and if the FPC RTL etc. attempts to replicate it it should be marked as such.

MarkMLl

[MarkMLl]

I've spent a bit more time on this, and have updated what's at https://github.com/MarkMLl/testdb (but not the files posted to the forum).

This now reaches into the PostgreSQL client state to recover the server IP address (previously, only the name /or/ address used for connection was exposed), removing the need for a messy lookup. It check both that the server thinks it's still got a connection (using ident i.e. port 113), and that the client address hasn't been changed recently (which was OP's apparent problem). As such it's a fairly good indication that trying to (e.g.) update a DBGrid is doomed to failure, although no attempt at reconnection etc. is attempted (as discussed earlier, I believe that would be problematic in the general case).

It also has a demonstration of setting a non-default transaction isolation level, although that's not been thoroughly exercised.

Hoping it is of some use to somebody at some point.

MarkMLl