Re: SPI_connect on multi-threaded app

On Feb21, 2014, at 13:44 , John Williams <jdwilliams1982(at)gmail(dot)com> wrote:
> I'm writing a pgsql extension in C, which is multithreaded. The SPI
> connection is global, so do I have to implement a lock to make sql
> queries in each thread, or can I make a connection on a per-thread basis?

Postgres backends aren't multi-threaded, and offer no support for
multiple threads whatsoever. AFAIK, the only safe way to use multiple
threads is to either restrict calls to *any* postgres function to only
one thread, or to use a single, big mutex to prevents multiple threads
from accessing postgres internals simultaneously.

You should also prevent auxiliary threads from executing the signal handlers
that postgres installs. See pthread_sigmask.

And you'll need to make sure that everything (i.e. the whole of postgres
and your extension) is built with multi-threading enabled. Otherwise,
things like errno might end up not being thread-local, meaning any syscall
your auxiliary threads do can interfere with postgres' error handling.

You might want to check whether you can run the multi-threaded parts
of your extension as a separate process, and communicate with the backend
via some IPC mechanism.

best regards,
Florian Pflugs