mORMot + PostgreSQL: "lost synchronization with server" when switching

Kabiri · 2026-04-30 19:42:07

@ttomas
Ok - Thanks

---Edit---
It failed and generated 9 log files. However, after reviewing the logs, I couldn’t identify the problem.

Last edited by Kabiri (2026-05-01 10:29:59)

ab · 2026-05-01 13:09:42

Compress the logs in a zip and make a link here.

Kabiri · 2026-05-01 15:39:47

ab wrote:

Compress the logs in a zip and make a link here.

Log file :
https://mega.nz/file/s5hkBChA#geEYuBua8 … cJDp4KyHhw

ttomas · 2026-05-01 17:13:28

You must provide source of init of your server/services and code of your interface using orm/dbparams. For test, what type of http server you use sync/async and how many threads?
From log:
Vi 260430200213 6714 is main init thread all OK
Vi 260430200227 2c24, Vi 260430200228 3ad0 looks like working threads and all is OK, on top you have pg db connection
Vi 260430200601 5cc8 and Vi 260430200702 6658 threads don't have own connection and this is the problem, you use same db connection from different threads!
Look like interface created threads using db connection in wrong way, provide some source for ViTaskService.GetDetail, ViTaskService.ViewTask.
Or you call this methods from different thread!

Last edited by ttomas (2026-05-01 17:44:24)

ttomas · 2026-05-01 18:36:14

message type 0x31 arrived from server while idle
message type 0x32 arrived from server while idle
message type 0x54 arrived from server while idle
message type 0x44 arrived from server while idle
message type 0x43 arrived from server while idle
message type 0x5a arrived from server while idle
in Vi 260430200702 6658 confirm using same connection in multiple threads

ab · 2026-05-01 18:47:01

There is also something wrong: "prepare" is done for each statement, which does not make any sense.
They are supposed to be cached and reused.

Please don't use ptOneFilePerThread, but ptIdentifiedInOneFile so that we could easily see using SynLogViewer the parallel process.

How do you setup the connection in your code?
Are you sure you are using ThreadingMode = tmThreadPool for the connection properties?

How are your services setup?
Don't use sicPerThread for your services instance pattern but sicShared.
Also ensure you don't touch the TInterfaceMethodOptions unless you know what you are doing.

Kabiri · 2026-05-02 09:09:41

The database connection is set up like this:

{$ifdef DBZeos}
  DBProps := TSqlDBZeosConnectionProperties.Create('zdbc:postgresql://' + Host,DBName, DBUser, DBPass);
{$else}
  DBProps := TSqlDBPostgresConnectionProperties.Create(Host, DBName, DBUser, DBPass);
{$endif}
DBProps.UseCache := False;
DBProps.ReconnectAfterConnectionError := True;

*DBZeos is not being used.

I set ptOneFilePerThread.
I did not change the connection properties threading mode, so it is still using the default value.
As you suggested earlier, I already switched to sicShared. The previous log was also generated with that setting.
I have not changed TInterfaceMethodOptions at all.
For each table, I register it like this:
VirtualTableExternalRegister(Model, TOrmAlarm, DBProps);
And each service is defined like this:
Server.ServiceDefine(TViAlarmService, [IViAlarmService], sicShared);
Inside the services, I only use ORM.
New log : https://mega.nz/file/8k4SUYyQ#f5pgGyFb6 … 7_m5hd2B7s

ttomas wrote:

Look like interface created threads using db connection in wrong way, provide some source for ViTaskService.GetDetail, ViTaskService.ViewTask.
Or you call this methods from different thread!

I implemented the interface the same way as the others. Only ORM is used inside these methods.
ViewTask only receives an ID and performs a simple update.
GetDetail reads values from several tables and puts them into a single JSON object.

Last edited by Kabiri (2026-05-02 09:11:48)

flydev · 2026-05-02 09:33:57

Kabiri wrote:

The database connection is set up like this:

{$ifdef DBZeos}
  DBProps := TSqlDBZeosConnectionProperties.Create('zdbc:postgresql://' + Host,DBName, DBUser, DBPass);
{$else}
  DBProps := TSqlDBPostgresConnectionProperties.Create(Host, DBName, DBUser, DBPass);
{$endif}
DBProps.UseCache := False;
DBProps.ReconnectAfterConnectionError := True;

but where?

please share the small "reproducible" sample you made the other day.

while writing this, I'm currently working on a hobby project - a custom RAG system that ingests all the mormot code into a postgre database using the pgvector extension. It uses an async http server, orm, and soa, and I haven't had any issues with many worker threads so far.

Last edited by flydev (2026-05-02 09:39:55)

Kabiri · 2026-05-02 10:58:56

The structure of this application is exactly the same as the main application. In this application as well, I didn’t encounter any errors, as I mentioned before.
However, this one uses a single table in the endpoints, while the main application uses multiple tables and more complex reports.

https://mega.nz/file/VgA0nBIa#Om-rx1smm … ZLXHMUxDeI

Kabiri · 2026-05-02 15:07:48

It happened in this sample code as well.
I also included the log files in the RAR archive.

https://mega.nz/file/Qs5XEBqD#s-JGX1rwX … xqj8PwZOFQ

ttomas · 2026-05-03 12:32:28

I can not reproduce you problem with source on post #60, with FPC, Ubuntu 24.04, mormot 2.4.14436!
If I view your log with SynLogView, filter only Trace, DB and one working thread 35, potentially is a bug in mormot when mormot.rest.sqlite3.TRestServerDB BeginCurrentThread/EndCurrentThread happen. It is hard to reproduce, you must start test, make pause and continue test and hit the time when Begin/EndCurentThread happen!
As@ab notice, to many Prepare in log, please set DBProps.UseCache := True;. Any reason for False?

flydev · 2026-05-03 12:37:39

I reproduced it with sample on #60 - on Delphi 12.3 (not tested against fpc yet) and only with useHttpSocket. If you set thread pool to, eg. 8 you can get the issue quite fast. On delphi, debugger almost stay silent and program hung. If an instance of the services is registered on server, with the orm instance passed on constructor, then the debugger can catch the AV easily on delphi 12.3.

Last edited by flydev (2026-05-03 12:40:08)

Kabiri · 2026-05-03 12:46:14

Thanks for checking. As I mentioned, it happens randomly — sometimes immediately after startup, and sometimes after an hour.

ttomas wrote:

please set DBProps.UseCache := True;. Any reason for False?

Since `DBProps.UseCache` was not explicitly set and I was getting this error, I changed it to `False`.

flydev · 2026-05-03 12:49:22

Kabiri wrote:

... it happens randomly ...

it's happening each run if using a good workload. no exception, it happen always.

and useHttpAsync doesn't suffer from it.

i saved screenshots yesterday:

Last edited by flydev (2026-05-03 12:51:43)

Kabiri · 2026-05-03 12:53:27

@flydev
Thanks for checking.

Sometimes it stops inside `mormot.db.sql.postgres`.
As I mentioned earlier, I had already pointed out which function it stops in.

ab · 2026-05-03 12:56:59

So just use useHttpAsync in in the meantime.

Kabiri · 2026-05-03 13:08:22

ab wrote:

So just use useHttpAsync in in the meantime.

OK.

ttomas · 2026-05-03 13:14:19

@flydev, in you log did exception happen when TRestServerDB Begin/End CurrentThread happen? Or in @kabiri happen by chance.

flydev · 2026-05-03 13:25:07

Im not on computer atm, will tell you that in an hour or so as I dont remember.

ttomas · 2026-05-03 13:36:33

I just reproduce in Linux with 8 threads, need to click for a long time :-), and yes happen after Begin/End CurrentThread.

flydev · 2026-05-03 15:08:24

ttomas wrote:

I just reproduce in Linux with 8 threads, need to click for a long time :-), and yes happen after Begin/End CurrentThread.

yes ok - confirmed on my side too.

Kabiri · 2026-05-03 15:55:21

ttomas wrote:

@flydev, in you log did exception happen when TRestServerDB Begin/End CurrentThread happen? Or in @kabiri happen by chance.

At the beginning of each log, I can see `BeginCurrentThread`, but I don’t see `EndCurrentThread`.
However, the error does not occur immediately after that.

mORMot Open Source

#51 2026-04-30 19:42:07

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#52 2026-05-01 13:09:42

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#53 2026-05-01 15:39:47

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#54 2026-05-01 17:13:28

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#55 2026-05-01 18:36:14

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#56 2026-05-01 18:47:01

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#57 2026-05-02 09:09:41

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#58 2026-05-02 09:33:57

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#59 2026-05-02 10:58:56

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#60 2026-05-02 15:07:48

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#61 2026-05-03 12:32:28

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#62 2026-05-03 12:37:39

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#63 2026-05-03 12:46:14

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#64 2026-05-03 12:49:22

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#65 2026-05-03 12:53:27

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#66 2026-05-03 12:56:59

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#67 2026-05-03 13:08:22

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#68 2026-05-03 13:14:19

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#69 2026-05-03 13:25:07

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#70 2026-05-03 13:36:33

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#71 2026-05-03 15:08:24

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

#72 2026-05-03 15:55:21

Re: mORMot + PostgreSQL: "lost synchronization with server" when switching

Board footer