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

[MarkMLl]

In short NO. This is normal operation of internet. So you can only look if your client can use a fixed IP.

A fixed IP could be guaranteed via a tunnel, but that still wouldn't prevent a connection being lost hence state being lost.

Connection /loss/ cannot be prevented, but it might be possible to /reestablish/ a connection transparently.

However as I've already said, row/table locks and transaction isolation state would be lost, so you'd have to check the table content explicitly.

It's not beyond the bounds of possibility that some of the PostgreSQL core people are sufficiently clued up on this that they know how to mine logs (journals) to reestablish state when an unexpectedly-disconnected session is reconnected. Hence my mention of pgPool etc.

Now I'm getting as sick of saying the same thing repeatedly as everybody else is of reading it :-)

MarkMLl

[Zvoni]

OK, let's condense this a bit.
1) I found this thread: https://forum.lazarus.freepascal.org/index.php/topic,38318.0.html
same author as this thread. I was actually thinking about "keep alives"
2) Change of IP on the client: --> Is this Client and the PG-Server on the same LAN, or is it the "Client reaches over the Internet to the Server" --> a.k.a is the Client in LA, and connects to the Server in NYC via Internet?

because if both are on the same LAN, you have max 254 "free" IP's on the smallest "private" Subnet (192.168.0.xxx).
Solution: since the server already has a static IP, assign static IP's to the client(s) and be done with it.
If it must be DHCP, your only chance IMO is to "compare" current IP to "saved" IP.
e.g. on startup you save the current IP to a variable (or wherever else). You connect, you do work, life is good.
Now your client sits idle for how many long hours, running into an IP-Change because of expiry of Lease?!??!
Before any action is taken (any CRUD-Statement), compare the then current IP to this saved IP.
If same, go on, if not, drop/close connection, and reconnect with that new IP, save that IP to that Variable.

3) IIRC, there was not long ago a thread discussing "best practice": connect at App-Startup, close on App-Shutdown versus before "CRUD-Action" connect, after "CRUD-Action" close connection

[MarkMLl]

because if both are on the same LAN, you have max 254 "free" IP's on the smallest "private" Subnet (192.168.0.xxx).
Solution: since the server already has a static IP, assign static IP's to the client(s) and be done with it.
If it must be DHCP, your only chance IMO is to "compare" current IP to "saved" IP.

If it's a LAN with DHCP, then it should be possible to allocate an unchanging lease identified by MAC address.

However in any case there's still the problem that any connection drop will result in loss of
NO CARRIER

[Zvoni]

If it's a LAN with DHCP, then it should be possible to allocate an unchanging lease identified by MAC address.

Which is, IIRC, the recommended way for static IP's on a private LAN

However in any case there's still the problem that any connection drop will result in loss of
NO CARRIER

Or if you do it as mentionend in "3)" above, trying to connect before a CRUD you'll get an exception if NO CARRIER (which implies a broken "physical" connection), you can catch, and then retry

[MarkMLl]

Which is, IIRC, the recommended way for static IP's on a private LAN

No, definitely not. If you do that you're making startup conditional on the DHCP server already being there: which it can't be, because the static address it needs is allocated by the local DHCP server...

The mandated way for a *static* IP is to have it hardwired in /etc/network/interfaces or equivalent, and that's how you do it for the router/DHCP/DNS that's normally at the bottom of your subnet's IP range.

If you just want an unchanging one then sure, use DHCP or whatever.

MarkMLl

[egsuh]

If both server and client are in the same LAN, this shouldn’t matter. If not, you can use DDNS service with your clienf PC as well.  Not sure whether this makes sense, but you will be able to fix “public” ip address of your clienf PC.

[MarkMLl]

If both server and client are in the same LAN, this shouldn’t matter. If not, you can use DDNS service with your clienf PC as well.  Not sure whether this makes sense, but you will be able to fix “public” ip address of your clienf PC.

I can assure you that an interrupted connection DOES matter.

MarkMLl

[egsuh]

I can assure you that an interrupted connection DOES matter.

Yes. I believe in you.

And I'm answering to following question:

So, my question is whether there is anything that can be done in the client Lazarus app (having TPQConnection and various bounded dbaware controls and long-running queries), to prevent loosing connection on the client side, when dynamic IP is changed on roter?

I do not know anything that can be done within Lazarus app. Instead, I'm suggesting to use DDNS service. It will install a small program on your (client) PC, and then your PC will be recognized with a fixed ip address over the world.

[tatamata]

I can assure you that an interrupted connection DOES matter.

Yes. I believe in you.

And I'm answering to following question:

So, my question is whether there is anything that can be done in the client Lazarus app (having TPQConnection and various bounded dbaware controls and long-running queries), to prevent loosing connection on the client side, when dynamic IP is changed on roter?

I do not know anything that can be done within Lazarus app. Instead, I'm suggesting to use DDNS service. It will install a small program on your (client) PC, and then your PC will be recognized with a fixed ip address over the world.

Can you recommend some DDNS service?

[tatamata]

If both server and client are in the same LAN, this shouldn’t matter. If not, you can use DDNS service with your clienf PC as well.  Not sure whether this makes sense, but you will be able to fix “public” ip address of your clienf PC.

Regarding this DDNS services, you mean services such Duck DNS or No-IP, right?
But, isn't it that they just map current dynamic IP with a fixed DNS?
So, the IP itself is not fixed, it is only the domain name that is fixed, am I right?
So, the PostgreSQL server will see the same dynamicaly changing client IP addresses as if there are no DDNS service on client side - isn't it so?
How can DDNS service help, when it does not fix IP address? Do I miss something here?

[Zvoni]

right.
Bottom Line: If your IP changes (for whatever reason) you have to reconnect to the server.
How to find out your IP has changed?
I've written above that you have to keep your "original" IP in a variable and permanently compare it to the "current" IP.
Nevermind, that in such a scenario you should maybe think to close the connection everytime your done with a CRUD-Statement, and reconnect before you fire off a Statement

And FWIW: That PG-Server doesn't have to know the (changed) IP-Address of the Client. The Server only cares about your connection request from the client.

Frankly, after all this discussion, i really don't see your problem.
I can't imagine a scenario, where you fire off a Statement, and the client has to run for hours being permanently "connected" to the server

[Чебурашка]

After this discussion, my suggestion is to create a test environment:

On client just open a conn, insert into the table a record, them make a select on it.

On server create db and the table, and add a trigger that executes a long operation (made of sleeps if available in pg or plain old for cycles).

On insert client will remain hung along with transaction for all time needed, and you can make all experiments about IP changing or other network stuff.

Should help validating all things said here.

[MarkMLl]

After this discussion, my suggestion is to create a test environment:

On client just open a conn, insert into the table a record, them make a select on it.

On server create db and the table, and add a trigger that executes a long operation (made of sleeps is available in pg or plain old for cycles).

On insert client will remain hung along with transaction for all time needed, and you can make all experiments about IP chaning or other network stuff.

Should help validating all things said here.

I agree.

Also the simple test of disconnecting the network cable and finding out what stops working. I'm of the impression that any active DB-aware (visual) control will be broken by a disconnection, but the question is how quickly it will be broken.

MarkMLl

[MarkMLl]

Instead, I'm suggesting to use DDNS service. It will install a small program on your (client) PC, and then your PC will be recognized with a fixed ip address over the world.

I'm uncomfortable with that. DDNS normally provides a fixed /name/ tracking a changed IP address, not a fixed IP address, and would typically be used to expose a server.

MarkMLl

[Чебурашка]

After this discussion, my suggestion is to create a test environment:

This should allow to make any test: IP change, DDNS, maybe VPN, remove cable...