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

[egsuh]

Regarding this DDNS services, you mean services such Duck DNS or No-IP, right?

You are 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?

No, you aren't. DHCP server will assign IP address to your PC dynamically, but DDNS transforms it to a fixed IP address. Any requests to that "fixed IP" will be processed by your PC.

[Zvoni]

I still don't understand, why his PG-Server has to know/remember the IP-Address of the client.
and he still hasn't answered if this setup is on a LAN, or via Internet.

Because if it's via internet, he would be exposing the server directly to the internet, which is a big NO-NO.
and DDNS for the client would be even a bigger NO-NO

[MarkMLl]

I still don't understand, why his PG-Server has to know/remember the IP-Address of the client.

It's the way TCP works: the IP address can't change during a session unless there's some sort of proxy in the middle.

So if a database connection is being kept open, either because there's traffic on it while an algorithm runs or because there are active DB-aware visual controls, the IP addresses have to be constant. And reestablishing a connection after it had gone down would result in lost transactions and state.

and he still hasn't answered if this setup is on a LAN, or via Internet.

Because if it's via internet, he would be exposing the server directly to the internet, which is a big NO-NO.
and DDNS for the client would be even a bigger NO-NO

I'm uncomfortable about all of this DDNS business. It normally provides a /name/, not a fixed IP (fixed IPs are a resource now almost entirely depleted, hence aren't normally given away free).

Apart from that, an exposed PostgreSQL server isn't /too/ big a deal provided that SSL etc. is being used properly, or it's accessed via a point-to-point tunnel. But even a point-to-point tunnel would be interrupted if the bearer (i.e. a domestic Internet link etc.) went down.

MarkMLl

[Zvoni]

I still don't understand, why his PG-Server has to know/remember the IP-Address of the client.

It's the way TCP works: the IP address can't change during a session unless there's some sort of proxy in the middle.

So if a database connection is being kept open, either because there's traffic on it while an algorithm runs or because there are active DB-aware visual controls, the IP addresses have to be constant. And reestablishing a connection after it had gone down would result in lost transactions and state.

and he still hasn't answered if this setup is on a LAN, or via Internet.

Because if it's via internet, he would be exposing the server directly to the internet, which is a big NO-NO.
and DDNS for the client would be even a bigger NO-NO

I'm uncomfortable about all of this DDNS business. It normally provides a /name/, not a fixed IP (fixed IPs are a resource now almost entirely depleted, hence aren't normally given away free).

Apart from that, an exposed PostgreSQL server isn't /too/ big a deal provided that SSL etc. is being used properly, or it's accessed via a point-to-point tunnel. But even a point-to-point tunnel would be interrupted if the bearer (i.e. a domestic Internet link etc.) went down.

MarkMLl

Mark, i know all that.
What i don't understand, why he needs to keep the client running/connected for so much time, that he runs into a lease expiry!

e.g. in Germany any "private" (homeuse) routers reconnect after 24-h to the Internet via their ISP, receiving a new PUBLIC IP-Address, so in such a scenario, yes, the IP-Address saved in the session-manager of the "remote" PG-Server would be "wrong".
BUT: he would only need the "correct" IP-Address of the Client, if he EXPECTS something back from such a long running Query (or whatever it is), and that's what i can't wrap my mind around.

[egsuh]

No, you aren't. DHCP server will assign IP address to your PC dynamically, but DDNS transforms it to a fixed IP address. Any requests to that "fixed IP" will be processed by your PC.

This isn't right. DDNS services link a domain to a fixed PC. I was confused with other links.

But it says that the IP address of a home router would not change unless its power is off. So, connecting via a home router might be a solution.

[Чебурашка]

why he needs to keep the client running/connected for so much time

I think the OP has explained that: it is like this because he implemented his software like that (now knowing that is a problem, maybe not knowing it at the the time he or somebody else implemented it) and he does not want or cannot change because it would probably be too expensive. This is why he is asking if it possible to have some kind of workaround to the problem he is now facing.

[MarkMLl]

why he needs to keep the client running/connected for so much time

I think the OP has explained that: it is like this because he implemented his software like that (now knowing that could a problem, maybe not knowing it at the the time he or somebody else implemented it) and he does not want or cannot change because it would probably be too expensive. This is why he is asking if it possible to have some kind of workaround to the problem he now facing.

Agreed, and I think we've given him some stuff to think about.

One issue is that even if he was running a lengthy query on the server and saving the result into a table (i.e. no text output), psql etc. would require a live connection until finished. But I DID suggest that that could be handled by running it in Gnu shell.

Apart from that, I strongly agree with the testing approach you suggested.

MarkMLl

[Zvoni]

why he needs to keep the client running/connected for so much time

This is why he is asking if it possible to have some kind of workaround to the problem he is now facing.

Yes, reworking that Query.
I can't fathom a SELECT which runs for hours on end, especially over the Internet, where everything is exposed from somebody to hack in

[MarkMLl]

But it says that the IP address of a home router would not change unless its power is off. So, connecting via a home router might be a solution.

Depends. It's like any other address allocated by DHCP, Radius etc.: it might remain stable even if the connection is interrupted, but that shouldn't be relied on.

But the fact of the matter is that while an IP address change will /definitely/ break any existing connections, an interruption at the bearer level (i.e. ADSL etc.) still /might/ break connections.

So long-running non-graphical sessions should be avoided if possible, and when unavoidable would probably benefit from being moved to a system "close" to the server. And I don't think we've arrived at a consensus relating to the robustness of DB-aware graphical controls.

MarkMLl

[MarkMLl]

I can't fathom a SELECT which runs for hours on end,

Anything that does a complex scan and processing. And as I've said, it will require a live session even if the output is going to a new table rather than being output as test.

especially over the Internet, where everything is exposed from somebody to hack in

Arguable. PostgreSQL uses SSL, which should be as secure as your access to your bank account. If you want more security use a point-to-point tunnel, pipe it over SSH, protect using port knocking etc.

The major problem with database servers isn't public access, it's SQL injection over badly-managed URLs which is another can of worms entirely.

MarkMLl

[Zvoni]

I can't fathom a SELECT which runs for hours on end,

Anything that does a complex scan and processing. And as I've said, it will require a live session even if the output is going to a new table rather than being output as test.

Even as an async call???? Say an async "SELECT ... INTO SomeTable..." or "INSERT .... FROM SomeSelectStatement"

[MarkMLl]

Even as an async call???? Say an async "SELECT ... INTO SomeTable..." or "INSERT .... FROM SomeSelectStatement"

Don't know. Async queries are definitely supported at the API level, but I don't know whether psql supports them... ditto for any query parsing done by the FCL etc.

As I've said, there's ways round that.

MarkMLl

[dseligo]

e.g. in Germany any "private" (homeuse) routers reconnect after 24-h to the Internet via their ISP, receiving a new PUBLIC IP-Address, so in such a scenario, yes, the IP-Address saved in the session-manager of the "remote" PG-Server would be "wrong".

That probably is his problem. He connects over Internet to SQL server and his router gets new address every 24h.
He said he can't change software design.

If queries are shorter than 24h then he could go away with resetting connection before connecting to SQL server.
But he said that sometimes queries took days.

IMHO, in his case it's best to upgrade Internet connection to one where connection isn't reset every 24h. I know some ISP's used to offer permanent IP address as additional option.

[Zvoni]

e.g. in Germany any "private" (homeuse) routers reconnect after 24-h to the Internet via their ISP, receiving a new PUBLIC IP-Address, so in such a scenario, yes, the IP-Address saved in the session-manager of the "remote" PG-Server would be "wrong".

That probably is his problem. He connects over Internet to SQL server and his router gets new address every 24h.
He said he can't change software design.

If queries are shorter than 24h then he could go away with resetting connection before connecting to SQL server.
But he said that sometimes queries took days.

IMHO, in his case it's best to upgrade Internet connection to one where connection isn't reset every 24h. I know some ISP's used to offer permanent IP address as additional option.

No, his problem is the design of the Software, that requires a permanent connection for such long running queries.
That said: I'm out of here

[MarkMLl]

I've had a relatively quiet few days so have been using them to try to catch up with various stuff. However things are warming up again so I anticipate that I will have to drop everything: as such, this is where I'm at.

The attached test program assumes the existence of a PostgreSQL server, but should be trivially modifiable for others. Assuming a R/W table in a suitable database, it sets up a DB-aware grid and allows records to be added/deleted, there is an operation button to force a commit or rollback. All of this works provided that the connection is kept live.

If the operation button is used to glitch the connection briefly, operation appears to continue. I've not investigated how tolerant this is to e.g. forced TCP session interruption.

If it is run on a laptop which is put to sleep, then after some interval the server will time-out the connection: there is no mechanism to reinstate it when the laptop is woken. At this point the test program will accept modifications to the grid, but attempting to commit the transaction will fail.

If, from the POV of the Postgres server, the client IP address changes, I would expect that to similarly result in a transaction failure. That also includes the situation where the link between the client and server is NATted and the connection tracking is interrupted for some reason.

This brings me back to the position that the naive RAD approach of plonking a DB-aware grid onto a form and expecting it to work is inadequate.

There might be ways of recovering from this and reestablishing the connection, even if the client IP address changes, but I have seen no coherent discussion of this. In any event, this would be unable to reinstate the state of the failed transaction, with the result that the content of the table (on the server) and DB-aware visual controls (on the client) would have to be assumed to be inconsistent.

In addition, I can find very little coherent information on specifying transaction isolation level using the standard components: there's some level of support for FireBird, but not for others.

So to summarise, one has to be extremely cautious and defensive when talking to a (remote) database, and while it's possible to use classic SQL techniques to handle individual rows any attempt to edit a table using a grid is risky.

Updated to add: the version of the program I posted monitors the DB controls' status. Rerunning the test suggests that the server-side timeout is of the order of three hours (with default server settings), the failure triggered an exception on the client reporting a TPQConnection preparation error, but there is no indication of this in the state of the controls (i.e.whether anything has been marked closed etc.).

My setup here has a laptop with WiFi to a local desktop system with DHCP etc., which NATs the connection to the database server. As such it's very similar to a domestic installation where the ISP doesn't guarantee stable IP allocation, or can introduce various connection tracking glitches (potentially destroying the connection-oriented TCP session).

If the connection was, in fact, permanently up (i.e. but with a very long-running query) then I anticipate that there would be various stay-alive traffic; in extremis something on the server could send notification messages (supported by controls for Postgres and FireBird, I believe that at least some other servers have something comparable at the API level). However I really cannot see any defence against the client IP address changing: the only people who could even start to address this would be the ones working on pgPool etc.

MarkMLl