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
