Asynchronous working DataSets?

[aehimself]

Any async code inside Zeos will add much of complicated code and create classes, that do many things. Clearly, it is not a good design.
Have a look at APL in TComponent. Or System.Net.Socket.TSocket. Begin*() all over the code. What for? ZMethodInThread is good enough for start.
In this context TZMemTable is a blessing: run query in background, as a result copy it in sync manner to TZMemTable.

Forcing stuff in background threads is really easy. As Fr0st mentioned earlier the real challenge is converting everything to be thread-safe. We have two options here:
1, Take the concept only and build everything up from 0. Codebase perspective this is a better solution as everything will be designed with thread-safety in mind. However it is extremely time- and resource consuming, and it effectively would be a different component at the end.
2, Patch things until they work. This is the quicker solution as you keep 80-90% of the existing code. Con is, the solution will be more complex, less robust and more prone to bugs.
Considering the small team behind Zeos; if we ever start moving toward async, it will be option #2 and the code will be more complex at the end.

In the end, people will use their own threads, just give them ability to thread-safely AbortOperation(). The real question is: can we call AbortOperation() from another thread using the same connection. In TZPostgreSQLConnection implementation it uses: FConn, FLogMessage, HandleErrorOrWarning(). Or asking another way: what operations of zeos classes that use Connection can affect those fields/method? My small knowlegle of Zeos and gut feeling tells me that those 2 questions are apt for another forum thread (with pun intended).

The concept of ZMethodInThread (and my later full-blown worker threads) do exactly that; I even implemented calling .AbortOperation on the connection if .Terminate gets called on the thread.
As for your concerns about .AbortOperation I'm not sure I get what you mean. Some libraries require a different connection (like MySQL) and that is already being taken care of by Zeos. I personally tested MySQL, Oracle, MSSQL (via DBLib) and SQLite; calling .AbortOperation from the VCL thread's context always worked and brought the expected results.

[marsupilami]

Any async code inside Zeos will add much of complicated code and create classes, that do many things. Clearly, it is not a good design.

Erm - why is adding classes to a library an indicator of a bad design? Did you take a look at TZQuery? It already has (a lot of) complicated code.

Have a look at APL in TComponent. Or System.Net.Socket.TSocket. Begin*() all over the code. What for? ZMethodInThread is good enough for start.
In this context TZMemTable is a blessing: run query in background, as a result copy it in sync manner to TZMemTable.

No - ZMethodInThread isn't good enough. Even when combined with TZMemTable. The idea behind all this is to keep the simplicity and RAD aspects of Delphi. If I have a Query component, I expect it to not only fetch data from the database and display it but also to post changes back to the database without me having to do a lot of work for this. Your suggestion doesn't allow for this. Also there are use cases where one has only one connection to the database but needs it to be accessible by several threads. Currently this is impossible with Zeos.

(Warning: personal context)
I personally think there is no other way around. For 2 years I am developing an app with Delphi PPL. I want to control my own threads, and taking that control away makes library hard to use in context of what I am doing. Example: I had to dig & hack Indy (a nice library on its own) to use it, because it hides server threads.
(End of Warning)

Why do you think that Zeos will handle things the same way Indy does? We are not Indy.

In the end, people will use their own threads, just give them ability to thread-safely AbortOperation().

No - most people simply don't want to care about threads. They want to get their work done. And if Zeos has an implementation that helps them, so be it. Take a look around in the forums. People don't get threading done correctly with zeod in a lot of cases.

The real question is: can we call AbortOperation() from another thread using the same connection. In TZPostgreSQLConnection implementation it uses: FConn, FLogMessage, HandleErrorOrWarning(). Or asking another way: what operations of zeos classes that use Connection can affect those fields/method? My small knowlegle of Zeos and gut feeling tells me that those 2 questions are apt for another forum thread (with pun intended).

AbortOperation is the only method one is allowed to call from another thread. All other Methods of TZConnection must be called from one thread only.

Async queue'ing is already done by external classes/libraries/frameworks. They are available for delphi.

Yes - and how does that help with my problem? I need that integrated with a query object that has the full capabilities anyone expects from TDataSet. Do you happen to know any implementation for this?

EDIT:
FLogMessage is used all over the TZPostgreSQLConnection.
TZPostgreSQLConnection.InternalClose does call FPlainDriver.PQFinish(Fconn). Docustring for InternalClose says
`A Connection is automatically closed when it is garbage collected. Certain fatal errors also result in a closed Connection.`
https://www.postgresql.org/docs/current ... Q-PQFINISH says `Closes the connection to the server. Also frees memory used by the PGconn object`.

Again: Zeos is thread safe as long as you stick to the rule that one connection and all components that are connected to it (TZQuery, TZStoredProc, ...) only get used by on thread at the same time.

Currently there are two ideas for this:
a) Implement a wrapper on the DBC layer that is thread safe and allows several TZConnection objects to share the same IZConnection interface. Then implement a way to clone the interface from one TZConenction object into another TZConenction object. This requires new objects for accessing metadata and the like too. Basically it is like implementing a new driver. But if done well it could integrate well with current Zeos applications.
b) implement a totally new TZAsyncConnection and a TZAsyncQuery object and go from there to see what happens. This could result in issues with code reuse from the normal TZConnection and TZQuery objects...

In any of these cases the idea is [*]not[*] to force all users to use this. The idea is to enably users to use this if they want to and if they think it can solve a problem for them.

[Scalmax]

Wow, 2 responces, both from main developers, in such short time. OK, first of all, I do understand & appreciate all that you wrote. Keep in mind I am new to Zeos. And it is my first DB library, that I use without help of other programmers. Lets go 1 by 1:

Erm - why is adding classes to a library an indicator of a bad design? Did you take a look at TZQuery? It already has (a lot of) complicated code.

Adding classes that do different things is OK. As for TZQuery - yes it has.

No - ZMethodInThread isn't good enough. Even when combined with TZMemTable. The idea behind all this is to keep the simplicity and RAD aspects of Delphi. If I have a Query component, I expect it to not only fetch data from the database and display it but also to post changes back to the database without me having to do a lot of work for this. Your suggestion doesn't allow for this. Also there are use cases where one has only one connection to the database but needs it to be accessible by several threads. Currently this is impossible with Zeos.

That was my try of reading the data only. Presented use cases (CUD from CRUD) cannot be realized with solution, that was proposed by me.

Why do you think that Zeos will handle things the same way Indy does? We are not Indy.

In any of these cases the idea is [*]not[*] to force all users to use this. The idea is to enably users to use this if they want to and if they think it can solve a problem for them.

I was selfishly afraid of taking thread control from me. My fear was premature. I totally agree with that direction.

No - most people simply don't want to care about threads. They want to get their work done. And if Zeos has an implementation that helps them, so be it. Take a look around in the forums. People don't get threading done correctly with zeod in a lot of cases.

I suspect that is the case and i do understand of merit of that approach.

Yes - and how does that help with my problem? I need that integrated with a query object that has the full capabilities anyone expects from TDataSet. Do you happen to know any implementation for this?

I do not know any such implementation in delphi/fpc. Just started making some parts on my own, that are suited for my needs.

Currently there are two ideas for this:

I am too fresh to asses presented approaches and give meaningfull & merit responce.

The part below is postgresql specyfic, maybe move it to another forum thread?

As for your concerns about .AbortOperation I'm not sure I get what you mean.

After analysis of source code, I suspect that there is a race condition on variables FLogMessage and Fconn, when we call AbortOperation() from another thread. Those variables are not mutex protected.
FLogMessage:
Worker thread kicked out from execution in between string handling. AbortOperation kicked in and tries to fill this variable.
Fconn:
libpq is reentrant (by relevant doc of postgres team). But I can image situation where just before calling AbortOperation() from another thread, some weird critical error caused InternalClose(). It stopped right after PQfinish() call (but before Fconn nulling) and then scheduler came and switch to AbortOperation.

[aehimself]

Disclaimer: take my answer with a grain of salt as I'm not using PostgreSQL anywhere. My reply has no experience, and only a quick code check behind it.

Fconn:
libpq is reentrant (by relevant doc of postgres team). But I can image situation where just before calling AbortOperation() from another thread, some weird critical error caused InternalClose(). It stopped right after PQfinish() call (but before Fconn nulling) and then scheduler came and switch to AbortOperation.

I mean this can happen to basically anything, not just ZConnection. We could put a nil check there but to be honest an exception is better as it blocks the code after to execute which might expect that an operation is aborted but we still have a connection.

FLogMessage:
Worker thread kicked out from execution in between string handling. AbortOperation kicked in and tries to fill this variable.

I mean the possibility is there but I see really low to zero chance that this will happen. The other thread will probably never be in that block, rather fetching / waiting for a .Post or something.
Remember, AbortOperation was designed to interrupt these long-lasting things, which is usually a query. Using it at random places might cause unexpected errors - I will be honest I never checked that scenario.

[Darsh]

AFAIU FireDAC from Delphi's std lib supports async, some ideas could be borrowed there. However, any semi-async implementation (as we know, there's no native language support of async operations) could be a problem source instead of a handy tool if a user is not doing it right. F.ex.:

Dataset.OpenAsync;
Dataset.FetchAll; // BADABOOM

If an internal result set could be hidden from such misuses - that would be fine. Otherwise it's better to populate things like RunOperationInThread where a user is supposed to know what he's doing

Acknowledging that FireDAC in Delphi's std lib supports async, caution is advised on potential misuses in semi-async implementations. Suggesting protective measures like hiding internal result sets or guiding users toward responsible practices, like using RunOperationInThread, to avoid unintended issues when handling async operations.