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

[Peter J. Holzer]

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.
| |   | h...@hjp.at | -- John Gordon
__/   | http://www.hjp.at/ |http://downlode.org/Etext/alicebob.html


signature.asc
Description: Digital signature

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

[Qiu Xiafei]

Thanks for your reply.

Because of the one-backend-per-session concept of PG, I think I should bind
one my DSL session to one bg worker only. It seems work. But is there a way
to launch a bg worker when a new session starts, just like pg's
per-session-backend do? Is it possible to run a bg worker for incoming
sessions and to launch a new bg worker to handle the session when it comes?

On Saturday, December 10, 2016, Michael Paquier 
wrote:

> On Fri, Dec 09, 2016 at 02:37:58PM -0800, Andres Freund wrote:
> > On 2016-12-09 16:52:05 +0800, Qiu Xiafei wrote:
> > > 1. Is there a way to use SPI in multi-thread style?
> >
> > No.
> >
> > > 2. Another option is to use libpq, like normal clients do. Is libpq as
> > > efficient as SPI?
> >
> > No.
>
> To give more details here, Postgres relies heavily on the fact that
> sessions
> working in parallel on the backend should be done in separate processes,
> like for transaction or snapshot handling.
> --
> Michael
>

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

[Michael Paquier]

On Sun, Dec 11, 2016 at 2:39 PM, Qiu Xiafei  wrote:
> Because of the one-backend-per-session concept of PG, I think I should bind
> one my DSL session to one bg worker only. It seems work. But is there a way
> to launch a bg worker when a new session starts, just like pg's
> per-session-backend do? Is it possible to run a bg worker for incoming
> sessions and to launch a new bg worker to handle the session when it comes?

There is the concept of dynamic background workers in Postgres. That's
what for example parallel query uses. At planning a number of workers
thought as suited is selected, and then spawned dynamically at
execution, at the limit defined by max_worker_processes though. Have a
look at worker_spi in the code tree, which is a module that does
present a way to spawn workers dynamically. This infrastructure could
allow for example anybody to re-create what autovacuum does with a
launcher process and workers created depending on what needs to be
cleaned up per database as a plugin.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Michael Paquier]

On Fri, Dec 09, 2016 at 02:37:58PM -0800, Andres Freund wrote:
> On 2016-12-09 16:52:05 +0800, Qiu Xiafei wrote:
> > 1. Is there a way to use SPI in multi-thread style?
> 
> No.
> 
> > 2. Another option is to use libpq, like normal clients do. Is libpq as
> > efficient as SPI?
> 
> No.

To give more details here, Postgres relies heavily on the fact that sessions
working in parallel on the backend should be done in separate processes,
like for transaction or snapshot handling.
-- 
Michael


signature.asc
Description: PGP signature

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

[Andres Freund]

On 2016-12-09 16:52:05 +0800, Qiu Xiafei wrote:
> 1. Is there a way to use SPI in multi-thread style?

No.

> 2. Another option is to use libpq, like normal clients do. Is libpq as
> efficient as SPI?

No.

- Andres


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general