[Lazarus] Multithreaded app with PostgreSQL connection

[Michael Van Canneyt]

On Mon, 20 Jun 2016, Ondrej Pokorny wrote:

> Hello!
>
> I have a multithreaded application where every thread needs to connect 
> to the same PostgreSQL database. How to design the DB connectivity for a 
> multithreaded application correctly?

Best is to have every thread use it's own connection.

If you want only a single connection, then you should make sure all Database
and Transaction properties (plus setting them to Nil) are protected by a
critical section: there are non-threadsafe lists involved.

>
> I see that TPQConnection has an FConnectionPool. So is it thread-safe?

Not if you don't follow the above steps.

>
> Create a TPQConnection in the main thread; create 
> TSQLQuery+TSQLTransaction in a (different) thread and connect it to to 
> the TPQConnection from main thread via the Database property?
> Or do I need a TPQConnection per thread?

Yes.

>
> I somehow failed to find any information about sqldb and multithreading:
> http://www.freepascal.org/docs-html/fcl/sqldb/index.html
> http://wiki.freepascal.org/SQLdb_Programming_Reference

Because it is not thread safe. It is on my todo list.

Michael.