Re: [GENERAL] Is is safe to use SPI in multiple threads? - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: [GENERAL] Is is safe to use SPI in multiple threads?
Date
Msg-id 20161223124124.GB9067@hjp.at
Whole thread Raw
In response to [GENERAL] Is is safe to use SPI in multiple threads?  (Qiu Xiafei <qiuxiafei@gmail.com>)
Responses Re: Is is safe to use SPI in multiple threads?
List pgsql-general
On 2016-12-09 16:52:05 +0800, Qiu Xiafei wrote:
> I'm new to PG and want to implement my domain-specific system based on PG. I
> wish to arrange my data as several tables in database and translate my DSL into
> SQL statements for query. Since one DSL statement may be mapped to several SQL
> statements, it's better to push the DSL server as close to the PG server as
> possible. I found PG's backgroud worker meet my needs. I can setup a background
> worker bounded to PG server and listen to a port for network requests. 
>
> But I encounter a problem that the Server Programing Interfaces are not THREAD
> SAFE. There are some global variables defined like: SPI_processed,
> SPI_tuptable, etc. This limit to my DSL server to work in single thread mode
> which is quite inefficient.

I had a similar requirement. I solved it by moving the application logic
out of the stored procedures. All the stored procedure does is an RPC
call (I use ØMQ for that) to a server process and send the result back
to the client. The server process converts the request into multiple SQL
queries which can be processed in parallel.

The downside is of course that the communication overhead is much
higher (A minimum of 4 network messages per request). That's not a
problem in my case, but you mileage may vary.

The advantages in my opinion are:

* A standalone server process is easier to test and debug than a bunch
  of stored procedures.
* I can easily scale out if necessary: Currently my database and server
  process run on the same machine, but I could distribute them over
  several machines with (almost) no change in logic.

        hp

--
   _  | Peter J. Holzer    | A coding theorist is someone who doesn't
|_|_) |                    | think Alice is crazy.
| |   | hjp@hjp.at         | -- John Gordon
__/   | http://www.hjp.at/ |    http://downlode.org/Etext/alicebob.html

Attachment

pgsql-general by date:

Previous
From: nomad@null.net
Date:
Subject: [GENERAL] Bug? Netmask of CIDR as TEXT has trailing masklen
Next
From: Günce Kaya
Date:
Subject: Re: [GENERAL] Er Data Modeller for PostgreSQL