[Lazarus] Multithreaded app with PostgreSQL connection

[Mark Morgan Lloyd]

Michael Van Canneyt wrote:
> 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 
> Databaseand Transaction properties (plus setting them to Nil) are 
> protected by acritical 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.

I was going to post a pointer to previous discussion at 
http://comments.gmane.org/gmane.comp.ide.lazarus.general/46205 but I 
think Michael's comment here overrides anything that's been said 
previously :-)

Irrespective of whether the underlying components are thread-safe, I 
think it's advisable to protect them from reentry with a critical 
section. There's just too much to go wrong in libpq etc., and in 
particular there's a risk of mayhem if somebody migrates an app to a 
database less-tolerant.

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]