Re: Is is safe to use SPI in multiple threads?

On 23/12/2016 13:41, Peter J. Holzer wrote:
> 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
>

Sorry to revive such an old topic. I am facing a similar requirement
where I am running multiple queries concurrently. Like Qiu Xiafei, I am
looking at SPI, and dynamic background workers. In particular, I am
using SPI_execq(...) on each dynamic background workers I spawn. What I
am experiencing is that I am not seeing a speedup, and I am beginning to
wonder if I have done something wrong, if the overheads are too big, or
if there are some limitations I am not aware of.

As I see that none of the comments here make much of a reference to
performance/speedup, would you be so kind as to tell me how satisfied
you were with performance? Any insights would be greatly appreciated.

Thanks,
Tom