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

[Чебурашка]

I have a desktop application connecting to remote PostgreSQL server, using TPQConnection.
It happens that during very long-running queries connection is lost due to my dynamic IP address changed on router.
Is there something that can be done in the app to prevent this?

In general is difficult to say because there could be any sort of long time operations one could imagine (for example operating on a genetic database with some PB of data could be some time counsuming).

I'd either split this long time db activity into smaller chucks so that they can be managed by shorter time living connections, with the capacity of doing a retry if one of this small chuck of operations fails, or even far better, move the long time computation on the server, with some sort of RPC. Anyway, also in the latter situation, one could have the need to managing server temporary unavailability, therefore there could still be the need to splitting the operations into short living chunks with the capacity of recovering from one chunck's operation fault.

[MarkMLl]

In general is difficult to say because there could be any sort of long time operations one could imagine (for example operating on a genetic database with some PB of data could be some time counsuming).

OP's already clarified that he's interested in both:

Actually all of that: there are visual controls (for example dbgrids) bound to Postgres tables, and also there are long-running fuzzy data matching queries. that can have runtime for hours or even days.

The long-running stuff should obviously first be checked for bad indexing etc. using EXPLAIN VERBOSE, and could then be handed off to something running on the server which saved its results in a newly-created table. It's arguable whether that side of things is within the scope of FPC/Lazarus.

The issue of DB-aware controls apparently wanting a live connection definitely is.

MarkMLl

[Чебурашка]

The issue of DB-aware controls apparently wanting a live connection definitely is.

Prob the OP should add some more details on what his/her program actually does. For example it could be making database operations indirectly by doing these operations on the DB-aware grid shown data. Example: load data in grid, scan values and modify some of them due to a logic. This would be very heavy because the changes made to the db grid value should be reflected to the db, one by one (repeated billion of times).

[MarkMLl]

Prob the OP should add some more details on what his/her program actually does. For example it could be making database operations indirectly by doing these operations on the DB-aware grid shown data. Example: load data in grid, scan values and modify some of them due to a logic. This would be very heavy because the changes made to the db grid value should be reflected to the db, one by one (repeated billion of times).

Possibly. However at that point he should consider (a) whether he could do the "heavy lifting" using a non-GUI program relying on the FCL's DB capabilities rather than on the LCL's DB-aware graphical components and (b) if that would result in something that couldn't be run on the server (e.g. because it's not his) what could be done to set up a "nearby" computer or VM which wouldn't be at the mercy of Internet reliability.

Enormously complex manipulations can be described in SQL, and can often be optimised surprisingly well: this is an area of CS where having a computer "reason about an algorithm" is definitely valuable (however much crap is spouted by the CS fraternity elsewhere). Hence if one is able to log into the server and run psql inside e.g. Gnu Shell, one can get a lot done even without specialist backend programming.

But when a manipulation can't be done using SQL, one has to choose a programming environment that is supported on- or at least close to- the server. And I'm afraid that the days when a server administrator would happily install something like FPC on a server are long past: far better to learn PL/SQL etc.

MarkMLl

[Zvoni]

Logic dictates, that you don't need a "live" connection, since a TDataSet can be "anything" which provides a TDataSet (InMem-DB, JSONDataSet etc.), but don't have a connection

Can a local dataset cache the result of a remote query automatically and reliably?

Which I think echos OP's question.

MarkMLl

Should be pretty simple to find out:
Setup a DBGrid, setup the TSQL-Trinity, setup the Datasource, connect all components according to Doc's/Wiki.
During runtime, load/display "original" Query/Data.
Use local Var of TDataSet.
Assign/Copy Queryresult or DBGrid.DataSource.DataSet to that Var
Disconnect the Connection!!
Play around with the values of that Var
Assign that Var to the DataSet-Property of the DBGrid's DataSource, and refresh.

In a nutshell (As far as i understand it): Any DB-Aware Control needs a DataSource.
it doesn't have to be a TQSLQuery-Instance!!
logic implies, that you don't need a "live" connection to a Database

EDIT: From the Sidelines: Any "long running Query" should be "run" asynchronously (or at least in its own thread).
And that would even depend what kind of Query: is it a SELECT (something comes back) or is it a CUD (Nothing coming back)

[Чебурашка]

Prob the OP should add some more details on what his/her program actually does. For example it could be making database operations indirectly by doing these operations on the DB-aware grid shown data. Example: load data in grid, scan values and modify some of them due to a logic. This would be very heavy because the changes made to the db grid value should be reflected to the db, one by one (repeated billion of times).

Possibly. However at that point he should consider (a) whether he could do the "heavy lifting" using a non-GUI program relying on the FCL's DB capabilities rather than on the LCL's DB-aware graphical components and (b) if that would result in something that couldn't be run on the server (e.g. because it's not his) what could be done to set up a "nearby" computer or VM which wouldn't be at the mercy of Internet reliability.

Enormously complex manipulations can be described in SQL, and can often be optimised surprisingly well: this is an area of CS where having a computer "reason about an algorithm" is definitely valuable (however much crap is spouted by the CS fraternity elsewhere). Hence if one is able to log into the server and run psql inside e.g. Gnu Shell, one can get a lot done even without specialist backend programming.

But when a manipulation can't be done using SQL, one has to choose a programming environment that is supported on- or at least close to- the server. And I'm afraid that the days when a server administrator would happily install something like FPC on a server are long past: far better to learn PL/SQL etc.

MarkMLl

Personally I always perceived the DB-aware components as suitable only for "very basic" tasks, and stopped  using them many centuries ago.

I'd say the problem presented here propably needs some sort of architectural analysis especially because it seems a not trivial task, when it's about processings that take so long time it is definitelty worth understanding if they can be redesigned or managed differently (and in general, I'd say that DB-aware components tend to disappeary quite soon when it's about complex things).

[Чебурашка]

EDIT: From the Sidelines: Any "long running Query" should be "run" asynchronously (or at least in its own thread).

I agree in general, but in this case, also with this async approach, OP could anyway incur into connection broken issues for the reasons he had explained. Prob here the need is a more deep architectural revision of the entire system.

[tatamata]

Let me share some replies from chatGPT.

So now forum should become a "Chat GPT" validation/explanation place?

Better if Chat GPT or other things are seen as real sources of information and, as such, their answers' understandability/comprehensiveness should be in charge of them. I.e. once you ask to them, and you do not usnderstand or it does not work please refer to them, not to the forums.

I shared chatGPT mumbo-jumbo just for sake of maybe this could give somebody some useful idea, nothing else...

[tatamata]

The long-running stuff should obviously first be checked for bad indexing etc. using EXPLAIN VERBOSE, and could then be handed off to something running on the server which saved its results in a newly-created table. It's arguable whether that side of things is within the scope of FPC/Lazarus.

The issue of DB-aware controls apparently wanting a live connection definitely is.

MarkMLl

Ok, just trust me, the queries itself are optimized and indexes utilized. So, please let's not discuss on whether queries are optimized or not, this is not the issue here. The fact is that these queries are running for a long time due to inherent complexity, not due to bad query design or bad indexing etc...
Also, the remote PostgreSQL server has DNS and fixed IP address, the problem of connection break is not on the server side, but rather on the local client side where Lazarus client application resigns.
Obviously this 2-layer design of client/server application is problematic, I am aware of that. Currently there is no middle-ware, it's just remote postgreSQL server database and Lazarus desktop client app. I am fully aware that this is not good approach, but it is as it is, I cannot change that design at the moment.
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 shared chatGPT mumbo-jumbo just for sake of maybe this could give somebody some useful idea, nothing else...

I apologize, my tone was not the appropriate one.

[tatamata]

Logic dictates, that you don't need a "live" connection, since a TDataSet can be "anything" which provides a TDataSet (InMem-DB, JSONDataSet etc.), but don't have a connection

Can a local dataset cache the result of a remote query automatically and reliably?

Which I think echos OP's question.

MarkMLl

Should be pretty simple to find out:
Setup a DBGrid, setup the TSQL-Trinity, setup the Datasource, connect all components according to Doc's/Wiki.
During runtime, load/display "original" Query/Data.
Use local Var of TDataSet.
Assign/Copy Queryresult or DBGrid.DataSource.DataSet to that Var
Disconnect the Connection!!
Play around with the values of that Var
Assign that Var to the DataSet-Property of the DBGrid's DataSource, and refresh.

In a nutshell (As far as i understand it): Any DB-Aware Control needs a DataSource.
it doesn't have to be a TQSLQuery-Instance!!
logic implies, that you don't need a "live" connection to a Database

EDIT: From the Sidelines: Any "long running Query" should be "run" asynchronously (or at least in its own thread).
And that would even depend what kind of Query: is it a SELECT (something comes back) or is it a CUD (Nothing coming back)

Zvoni, it seems to me that you are presenting here read-only unbounded grids...what if you need to edit values and send back to database tables?

[Чебурашка]

the problem of connection break is not on the server side

I would not be so sure about this, because it is true that connection is started by client, but also server receives IP of client to establish initial connection. And once the client changes the IP maybe it is the server that goes in trouble in keeping the connection alive.

WDYT?

[MarkMLl]

Zvoni, it seems to me that you are presenting here read-only unbounded grids...what if you need to edit values and send back to database tables?

I agree, that's a real problem. Not just because of issues like picking one changed row out of a local table and using it to update the backend server, but also because of transaction isolation and so on.

However since the transaction isolation will be broken if the connection goes down...

I suspect that it would be necessary to do something like

* Check connection, start new transaction, possibly lock row or table.

* Check relevant row on backend is unmodified.

* If unmodified, update and commit.

* If modified, prompt user and require response within 15 secs so as not to extend transaction life.

* On confirmation, update and commit.

* Without confirmation, rollback with error handling on the client software.

This is obviously pretty horrid when compared with what we're used to from Delphi/Lazarus and for that matter apps in general, but from a user's POV is probably not much different from what he'd see on e.g. a Web-based banking site. The important thing is that a Web-based banking app wouldn't exhibit Total Inability To Support Unexpected Problems and go blank if there were a networking problem: the worst that would happen would be that it requested a repeat login before work could be continued.

MarkMLl

[af0815]

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?

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

[MarkMLl]

I would not be so sure about this, because it is true that connection is started by client, but also server receives IP of client to establish initial connection. And once the client changes the IP maybe it is the server that goes in trouble in keeping the connection alive.

Any interruption will cause a problem, irrespective of which end caused it. The session is connection-oriented i.e. TCP over IP, and each IP packet contains the two endpoint addresses rather than some sort of circuit number. If either end sends a packet to a broken connection it will either time-out or result in an explicit ICMP unroutable error, and I don't believe that there's any provision in the standard TCP/IP stack to track changing IP addresses. Either a repeated timeout or an ICMP error will result in the failure being propagated up the network stack to the server or client process, which will cause the server daemon process or application program to fail unless written to know how to cope with it. The server cannot cope with the client moving to an address that isn't known, the client could potentially cope with being moved to an unknown address since it would still know the server's address... subject to possible transaction isolation and locking issues, not to mention SSL.

Which is one reason why I raised proxies like pgPool right at the start of the thread: running that at the client end /might/ /possibly/ address some of these problems.

MarkMLl