Loosing postgres (TPQConnection) due to local dynamic IP address
change
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2023-07-14 19:11

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?

----------------------------------------------------------------------
MarkMLl · 2023-07-14 20:07

Do you really mean "long-running queries" (or for that matter long-
running transactions) or visual controls which assume that they can
hold a connection open indefinitely?

I'm not sure you really can protect against the former other than
finding some way to run detached queries on the server. The latter is,
I feel, a problem with any development environment which provides
visual controls, and unless something like pgPool would help I'm not
sure there's an easy way round it other than avoiding visual controls.

MarkMLl

----------------------------------------------------------------------
colo · 2023-07-15 10:44

If something changes the peer address (from the server's perspective)
right under your established TCP connection, there is nothing you can
do on the NATted client to prevent this session from breaking.

----------------------------------------------------------------------
tatamata · 2023-07-16 08:17

Quote from: MarkMLl on July 14, 2023, 10:07:02 pmDo you really mean
"long-running queries" (or for that matter long-running transactions)
or visual controls which assume that they can hold a connection open
indefinitely?

I'm not sure you really can protect against the former other than
finding some way to run detached queries on the server. The latter is,
I feel, a problem with any development environment which provides
visual controls, and unless something like pgPool would help I'm not
sure there's an easy way round it other than avoiding visual controls.

MarkMLl
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.

----------------------------------------------------------------------
tatamata · 2023-07-16 08:20

Let me share some replies from chatGPT.
"
Your assumption that the dynamic IP address change on your router
might be causing the loss of connection to the remote PostgreSQL
server is justified. Let me explain why.

When your computer connects to the internet via a home router, it
typically receives an IP address from your internet service provider
(ISP) dynamically. This means that the IP address can change over
time, especially if your internet connection drops or you restart your
router.

In the context of connecting to a remote PostgreSQL server from your
desktop application, you would typically configure the connection
settings with the IP address of the server. If your router's IP
address changes while your application is running, the connection
between your application and the PostgreSQL server can be disrupted.

In this scenario, your long-running query may still be executing on
the PostgreSQL server, but your application's connection to the server
is lost due to the IP address change. As a result, your application
won't be able to receive the query results or maintain any ongoing
communication with the server.

To address this issue, you could consider a few potential solutions:

1. Static IP address: Contact your ISP to inquire about obtaining a
static IP address for your internet connection. A static IP address
doesn't change, allowing your application to consistently connect to
the PostgreSQL server.

2. Dynamic DNS: Configure a dynamic DNS (Domain Name System) service
that associates a domain name with your changing IP address. You can
then use the domain name in your application's connection settings
instead of the IP address. The dynamic DNS service will update the
domain name with the new IP address whenever it changes.

3. Reconnect logic: Implement logic in your application to
periodically check the connection status and reconnect if it's lost.
This way, if your IP address changes, your application can reestablish
the connection to the PostgreSQL server automatically.

By implementing one or a combination of these solutions, you can
mitigate the issue of losing connection to the remote PostgreSQL
server due to a dynamic IP address change.
"

"
Yes, there are a few things you can do within your Lazarus/FreePascal
desktop application to mitigate the problem of losing connection to
the remote PostgreSQL server due to a dynamic IP address change. Here
are a couple of suggestions:

1. Connection monitoring: Implement a mechanism within your
application to monitor the connection status continuously. You can
periodically ping the server or send lightweight queries to keep the
connection alive. If the connection is lost, your application can
attempt to reconnect automatically.

2. Connection pooling: Consider using connection pooling in your
application. Connection pooling involves creating and managing a pool
of database connections that can be reused by multiple client
requests. With connection pooling, your application can maintain a set
of established connections to the PostgreSQL server, even if the IP
address changes. When a client request comes in, it can use an
available connection from the pool rather than establishing a new one.

By implementing these techniques, your application can proactively
monitor and manage the connection to the PostgreSQL server, increasing
its resilience to dynamic IP address changes and reducing the chances
of losing the connection during long-running queries.
"

"
Certainly! Here are code examples demonstrating the two suggested
approaches: connection monitoring and connection pooling.

1. Connection Monitoring:
```pascal
uses
  ..., DB, PQConnection;

var
  Connection: TPQConnection;

procedure MonitorConnection;
begin
  if not Connection.Connected then
  begin
    try
      Connection.Open;
      Writeln('Connection reestablished.');
    except
      on E: Exception do
        Writeln('Error reconnecting: ' + E.Message);
    end;
  end;
end;

begin
  Connection := TPQConnection.Create(nil);
  try
    Connection.HostName := 'your_postgresql_server_address';
    Connection.DatabaseName := 'your_database_name';
    Connection.UserName := 'your_username';
    Connection.Password := 'your_password';

    // Set up additional connection properties as needed

    while True do
    begin
      MonitorConnection;
      // Sleep or perform other tasks
    end;
  finally
    Connection.Free;
  end;
end.
```

In this example, we define a `MonitorConnection` procedure that checks
if the connection is lost (`Connection.Connected` returns `False`). If
the connection is not established, we attempt to reconnect using
`Connection.Open`. You can customize this code to suit your specific
needs, such as adding sleep intervals between connection checks.

2. Connection Pooling:
```pascal
uses
  ..., DB, PQConnection, Data.DBConnectionPool;

var
  ConnectionPool: TDBConnectionPool;

procedure InitializeConnectionPool;
var
  ConnectionFactory: TConnectionFactory;
begin
  ConnectionFactory := TConnectionFactory.Create(
    function: TCustomConnection
    begin
      Result := TPQConnection.Create(nil);
      Result.HostName := 'your_postgresql_server_address';
      Result.DatabaseName := 'your_database_name';
      Result.UserName := 'your_username';
      Result.Password := 'your_password';
      // Set up additional connection properties as needed
    end);

  ConnectionPool := TDBConnectionPool.Create(ConnectionFactory);
  ConnectionPool.MaxConnections := 10;
end;

procedure UseDatabaseConnection;
var
  Connection: TPQConnection;
  Query: TSQLQuery;
begin
  Connection := ConnectionPool.AcquireConnection;
  try
    Query := TSQLQuery.Create(nil);
    Query.SQL.Text := 'SELECT * FROM your_table';
    Query.Database := Connection;
    Query.Open;
    // Perform your database operations
    Query.Close;
    Query.Free;
  finally
    ConnectionPool.ReleaseConnection(Connection);
  end;
end;

begin
  InitializeConnectionPool;

  // Example usage of the connection pool
  UseDatabaseConnection;

  ConnectionPool.Free; // Remember to free the connection pool when
done
end.
```

In this example, we use the `TDBConnectionPool` class from the
`Data.DBConnectionPool` unit to create and manage a pool of PostgreSQL
connections. The `InitializeConnectionPool` procedure sets up the
connection factory, which creates instances of `TPQConnection`. The
`UseDatabaseConnection` procedure demonstrates how to acquire a
connection from the pool, use it for executing queries, and release it
back to the pool when finished.

Please note that these code examples provide a basic structure and may
require additional modifications to fit your specific application
requirements.
"
"
In a Lazarus/FreePascal desktop application, you would typically place
the code within the appropriate event handlers or procedures that are
relevant to your application's functionality. Here's a breakdown of
where you can put the code in a real application:

1. Connection Monitoring:
   - Place the code that sets up the `TPQConnection` and the
`MonitorConnection` procedure in the appropriate unit or module of
your application.
   - Consider placing the connection setup code in the application's
initialization section or a dedicated initialization procedure.
   - In the main program flow or within a background thread, call the
`MonitorConnection` procedure periodically to check the connection
status and attempt reconnection if necessary.
   - You can use a timer component or a loop with a sleep interval to
control the timing of the connection checks.

2. Connection Pooling:
   - Place the code that sets up the `TDBConnectionPool` and the
connection usage code in the appropriate unit or module of your
application.
   - Consider placing the connection pool setup code in the
application's initialization section or a dedicated initialization
procedure.
   - Depending on your application's structure, you can call the
`UseDatabaseConnection` procedure in response to user actions,
specific events, or within a specific business logic section.
   - Ensure that you properly handle exceptions and release
connections back to the pool even in case of errors.

Remember that these examples are just templates, and you should adapt
them to fit your application's specific architecture and requirements.
You may need to consider thread safety, error handling, and additional
components or units that your application uses.
"

----------------------------------------------------------------------
MarkMLl · 2023-07-16 09:28

Quote from: tatamata on July 16, 2023, 10:20:42 amLet me share some
replies from chatGPT.

We'd rather you didn't, and we'd particularly rather you didn't insert
large blocks of code without proper markup.

I'm sorry, but I've made some comments already and while I'm
interested in helping my time budget doesn't extend to reading that
stuff.

MarkMLl

----------------------------------------------------------------------
egsuh · 2023-07-16 11:10

AFAIK visual controls do not need to keep connection. You may
commitretaing to cut direct  connection.

If you mean remote server a computer on home router then you can fix
the internal ip address of the PC.

If remote server means a server that you want to publish on the
internet then you may use DDNS.

----------------------------------------------------------------------
MarkMLl · 2023-07-16 11:32

They certainly used to, and would get very unhappy if asked to handle
e.g. a resize if the connection went down. I have keen memories of a
weekend crash caused by Debian updating the PostgreSQL server which
necessitated a daemon restart, resulting in all current connections
being dropped by the client systems.

That might obviously have changed, or it might be possible to have
something as a placeholder which would defer the problem until the
next time a query has to be made to (re)populate a visual control with
some sort of reconnection at that point. But generally speaking this
is one of a number of design flaws which have affected RAD tools since
their earliest days of connecting to backend servers.

And there might obviously be OS-specific behaviour regarding existing
(TCP) connections if the endpoint IP address changed. Apart from
anything else, a change would be very likely to impact on
iptables/netfilter/routing etc. entries on Linux, or their equivalent
on other OSes.

The bottom line is that the overall SQL/relational architecture did
not allow for the case where connection duration was significantly
longer than query/transaction duration.

MarkMLl

----------------------------------------------------------------------
Zvoni · 2023-07-17 06:19

Quote from: tatamata on July 14, 2023, 09:11:58 pmI 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?
This sounds like a published port on the Internet, which is a Big NO-
NO!
Use a WebService to handle your requests

----------------------------------------------------------------------
af0815 · 2023-07-17 06:39

Quotethat can have runtime for hours or even daysSuch long running
statements should not done over a connection. This kind of
datagrabbing/analysing is more for a job on the server itself. A IP
connection can be lost, changed, slow, .. by design and this is
normal. 

BTW, here is no playground for ChatGPT answers. In other forums they
are simply deleted, because normal unwanted.

----------------------------------------------------------------------
MarkMLl · 2023-07-17 06:41

Quote from: Zvoni on July 17, 2023, 08:19:23 amQuote from: tatamata on
July 14, 2023, 09:11:58 pmI 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?
This sounds like a published port on the Internet, which is a Big NO-
NO!
Use a WebService to handle your requests

That's not how I read it. My interpretation was that his server was
somewhere else (e.g. at his office) but his home router changed IP
addresses and ripped down existing connections whenever the DHCP lease
was renewed or as the result of noise on the line. Those are, of
course, issues which should be resolved by the ISP- or if necessary by
changing ISP- but that's outside the scope of the question.

I think you're the Local Expert: does having DB-aware components
active require a live connection? If so, then what can be done about
it since that's not how SQL was intended to be used (with the
exception of PostgreSQL notifications etc.).

MarkMLl

----------------------------------------------------------------------
MarkMLl · 2023-07-17 06:48

Quote from: af0815 on July 17, 2023, 08:39:02 amBTW, here is no
playground for ChatGPT answers. In other forums they are simply
deleted, because normal unwanted.

Apart from anything else, they are extremely undesirable in a forum to
which people might be directed to by Google etc. when they're looking
for reliable information.

I didn't read it in detail because it wasn't marked up and because
I've got /rather/ a lot of other crap on my plate, but the impression
I got was that it didn't even address the question that OP was asking:
and that should have been obvious to him. Hence "playground" or
"sandpit" is very much the correct way of describing it.

So quite frankly my preference would be that he deleted that posting
(or at least removed all the ChatGPT stuff from it) at which point we
can also remove these messages being critical of him.

MarkMLl

----------------------------------------------------------------------
Zvoni · 2023-07-17 06:53

Quote from: MarkMLl on July 17, 2023, 08:41:58 amThat's not how I read
it. My interpretation was that his server was somewhere else (e.g. at
his office) but his home router changed IP addresses and ripped down
existing connections whenever the DHCP lease was renewed or as the
result of noise on the line. Those are, of course, issues which should
be resolved by the ISP- or if necessary by changing ISP- but that's
outside the scope of the question.Hmm... OK, agreed. could be
interpreted that way, that this "remote" server is on a LAN.
Now the question: Hast that server a static IP? (Which it should
have!!).
OTOH: Changing IP's could/should be avoided using a properly set up
DNS, and any connection should use hostnames


QuoteI think you're the Local Expert: does having DB-aware components
active require a live connection? If so, then what can be done about
it since that's not how SQL was intended to be used (with the
exception of PostgreSQL notifications etc.).

MarkMLl
Thanks for that vote of confidence 
I don't consider myself an expert (local or otherwise), and i'm
definitely the wrong person to ask about DB-bound controls, since i've
never used them (exactly because of those "questions" you've posted).
That said: any DB-aware Control (DBGrid etc.) have a TDataSource which
delegates to a TDataSet.
https://lazarus-ccr.sourceforge.io/docs/fcl/db/tdatasource.html
QuoteTDatasource is a mediating component: it handles communication
between any DB-Aware component (often edit controls on a form) and a
TDataset instance.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

----------------------------------------------------------------------
MarkMLl · 2023-07-17 07:31

Quote from: Zvoni on July 17, 2023, 08:53:34 amHmm... OK, agreed.
could be interpreted that way, that this "remote" server is on a LAN.
Now the question: Hast that server a static IP? (Which it should
have!!).

Definitely.

QuoteOTOH: Changing IP's could/should be avoided using a properly set
up DNS, and any connection should use hostnames

Partly agreed. Relying on a DNS server means that you've got two
possible servers which could fail, not just one. In the end, I prefer
to make a DNS query at the start of a program run, then to hold the IP
address in numeric form for as long as is reasonable.

QuoteThanks for that vote of confidence 
I don't consider myself an expert (local or otherwise), and i'm
definitely the wrong person to ask about DB-bound controls, since i've
never used them (exactly because of those "questions" you've posted).
That said: any DB-aware Control (DBGrid etc.) have a TDataSource which
delegates to a TDataSet.
https://lazarus-ccr.sourceforge.io/docs/fcl/db/tdatasource.html
QuoteTDatasource is a mediating component: it handles communication
between any DB-Aware component (often edit controls on a form) and a
TDataset instance.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

Hmm. OK, here's my position. I've got a large chunk of mission
critical code talking to a local server which uses DB-aware controls,
but this was something I originally wrote 20 years ago and I'm not
happy about the situation for reasons already discussed.

My understanding is that DB-aware components require a live
connection, and if that goes down they will at the very least become
unresponsive (i.e. unable to handle redraw events etc. without error).
I might be wrong in this or things might have moved on in 20 years,
but I think I've seen discussion of the hoops one has to jump through
to automatically reconnect and it wasn't a one-property fix.

SQL was originally designed to process a query, return a result, and
to be tolerant of interruption when not actively processing a query or
transaction. That was weakened slightly when cursors etc. were
introduced which allowed results to be returned in chunks.

Every RAD that I've seen relies on being able to hold the connection
open indefinitely. That is bad. Add to that that various DB-aware
office utilities etc. go badly wrong if asked to inspect an
unexpectedly-large table: that is also bad.

I put quite a lot of work into a testbed program a few years ago which
doubled-up the DB connection and was able to use one to request a data
update (I was using a local weather-station table as the experimental
backend) and the other to display the last-known data. I can't
remember exactly how well that worked, but in any event it was non-
trivial. It also used PostgreSQL's notification facility (predating
the LCL's support for this) to become aware of new data, and my
opinion is that the only reason a connection should be kept open is
for that type of asynchronous event.

So what's really needed- and I don't know where Lazarus stands on
this- is a way of making a query, parsing it into a control without
the assumption that there is still a live connection, and having
comparable facilities to send updates back to the server. Hence your

QuoteLogic 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

----------------------------------------------------------------------
Чебурашка · 2023-07-17 07:53

Quote from: tatamata on July 16, 2023, 10:20:42 amLet 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.

----------------------------------------------------------------------
Чебурашка · 2023-07-17 08:52

Quote from: tatamata on July 14, 2023, 09:11:58 pmI 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 · 2023-07-17 09:12

Quote from: Чебурашка on July 17, 2023, 10:52:07 amIn 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:

Quote from: tatamata on July 16, 2023, 10:17:33 amActually 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

----------------------------------------------------------------------
Чебурашка · 2023-07-17 10:10

Quote from: MarkMLl on July 17, 2023, 11:12:44 amThe 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 · 2023-07-17 11:02

Quote from: Чебурашка on July 17, 2023, 12:10:21 pmProb 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 · 2023-07-17 11:08

Quote from: MarkMLl on July 17, 2023, 09:31:28 amQuoteLogic 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)

----------------------------------------------------------------------
Чебурашка · 2023-07-17 11:33

Quote from: MarkMLl on July 17, 2023, 01:02:03 pmQuote from: Чебурашка
on July 17, 2023, 12:10:21 pmProb 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).

----------------------------------------------------------------------
Чебурашка · 2023-07-17 11:36

Quote from: Zvoni on July 17, 2023, 01:08:55 pmEDIT: 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 · 2023-07-17 12:11

Quote from: Чебурашка on July 17, 2023, 09:53:56 amQuote from:
tatamata on July 16, 2023, 10:20:42 amLet 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 · 2023-07-17 12:24

QuoteThe 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?

----------------------------------------------------------------------
Чебурашка · 2023-07-17 12:29

Quote from: tatamata on July 17, 2023, 02:11:03 pmI 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 · 2023-07-17 12:30

Quote from: Zvoni on July 17, 2023, 01:08:55 pmQuote from: MarkMLl on
July 17, 2023, 09:31:28 amQuoteLogic 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?

----------------------------------------------------------------------
Чебурашка · 2023-07-17 12:39

Quote from: tatamata on July 17, 2023, 02:24:14 pmthe 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 · 2023-07-17 13:10

Quote from: tatamata on July 17, 2023, 02:30:34 pmZvoni, 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 · 2023-07-17 13:24

Quote from: tatamata on July 17, 2023, 02:24:14 pmSo, 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 · 2023-07-17 13:25

Quote from: Чебурашка on July 17, 2023, 02:39:33 pmI 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

----------------------------------------------------------------------
MarkMLl · 2023-07-17 13:34

Quote from: af0815 on July 17, 2023, 03:24:21 pmIn 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 · 2023-07-17 13:40

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 · 2023-07-17 13:52

Quote from: Zvoni on July 17, 2023, 03:40:49 pmbecause 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 · 2023-07-17 14:01

Quote from: MarkMLl on July 17, 2023, 03:52:40 pmIf 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

QuoteHowever 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 · 2023-07-17 14:08

Quote from: Zvoni on July 17, 2023, 04:01:14 pmWhich 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 · 2023-07-17 16:34

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 · 2023-07-17 16:50

Quote from: egsuh on July 17, 2023, 06:34:35 pmIf 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 · 2023-07-18 03:35

QuoteI can assure you that an interrupted connection DOES matter.
Yes. I believe in you.

And I'm answering to following question:

QuoteSo, 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 · 2023-07-18 05:39

Quote from: egsuh on July 18, 2023, 05:35:46 amQuoteI can assure you
that an interrupted connection DOES matter.
Yes. I believe in you.

And I'm answering to following question:

QuoteSo, 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 · 2023-07-18 06:17

Quote from: egsuh on July 17, 2023, 06:34:35 pmIf 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 · 2023-07-18 06:24

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

----------------------------------------------------------------------
Чебурашка · 2023-07-18 06:53

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 · 2023-07-18 07:03

Quote from: Чебурашка on July 18, 2023, 08:53:54 amAfter 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 · 2023-07-18 07:06

Quote from: egsuh on July 18, 2023, 05:35:46 amInstead, 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

----------------------------------------------------------------------
Чебурашка · 2023-07-18 07:11

Quote from: Чебурашка on July 18, 2023, 08:53:54 amAfter this
discussion, my suggestion is to create a test environment:

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

----------------------------------------------------------------------
egsuh · 2023-07-18 08:08

QuoteRegarding this DDNS services, you mean services such Duck DNS or
No-IP, right?
You are right.

QuoteBut, 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 · 2023-07-18 08:18

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 · 2023-07-18 08:31

Quote from: Zvoni on July 18, 2023, 10:18:00 amI 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.

Quoteand 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 · 2023-07-18 08:46

Quote from: MarkMLl on July 18, 2023, 10:31:14 amQuote from: Zvoni on
July 18, 2023, 10:18:00 amI 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.

Quoteand 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 · 2023-07-18 08:54

QuoteNo, 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.

----------------------------------------------------------------------
Чебурашка · 2023-07-18 08:57

Quote from: Zvoni on July 18, 2023, 10:46:52 amwhy 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 · 2023-07-18 09:00

Quote from: Чебурашка on July 18, 2023, 10:57:43 amQuote from: Zvoni
on July 18, 2023, 10:46:52 amwhy 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 · 2023-07-18 09:02

Quote from: Чебурашка on July 18, 2023, 10:57:43 amQuote from: Zvoni
on July 18, 2023, 10:46:52 amwhy 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 · 2023-07-18 09:10

Quote from: egsuh on July 18, 2023, 10:54:06 amBut 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 · 2023-07-18 09:18

Quote from: Zvoni on July 18, 2023, 11:02:37 amI 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.

Quoteespecially 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 · 2023-07-18 09:34

Quote from: MarkMLl on July 18, 2023, 11:18:09 amQuote from: Zvoni on
July 18, 2023, 11:02:37 amI 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 · 2023-07-18 09:44

Quote from: Zvoni on July 18, 2023, 11:34:28 amEven 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 · 2023-07-18 11:45

Quote from: Zvoni on July 18, 2023, 10:46:52 ame.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 · 2023-07-18 12:02

Quote from: dseligo on July 18, 2023, 01:45:42 pmQuote from: Zvoni on
July 18, 2023, 10:46:52 ame.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 · 2023-08-03 09:01

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

----------------------------------------------------------------------
MarkMLl · 2023-08-08 07:42

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 · 2023-08-12 14:26

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