Re: Tips/advice for implementing integrated RESTful HTTP API - Mailing list pgsql-hackers

<p dir="ltr">Hmm yes I am learning that the BG worker system isn't as helpful as I had hoped due to the single database
restriction.<pdir="ltr">As for a writing a frontend this might be the best solution. <p dir="ltr">A java frontend would
beeasy but pointless because the whole point here is to provide a lightweight access method to the database for
environmentsthat don't have the ability to use the jdbc or libpq libraries.  Deploying a java setup would be too much
trouble.<pdir="ltr">I do see now that PG uses one worker per connection rather than a worker pool as I had thought
before.So there's nothing already in there to help me dispatch requests and making my own worker pool that distributes
requestsusing sockets wouldn't be any better than connecting back using libpq.<p dir="ltr">A C frontend using libevent
wouldbe easy enough to make and deploy for this I guess.<p dir="ltr">But... Maybe nobody really wants this thing
anyway,there seem to be some other options out there already.<p dir="ltr">Thanks for the feedback.<br /><div
class="gmail_quote">OnAug 31, 2014 8:46 PM, "Craig Ringer" <<a
href="mailto:craig@2ndquadrant.com">craig@2ndquadrant.com</a>>wrote:<br type="attribution" /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> On 08/31/2014 12:40 PM, Dobes
Vandermeerwrote:<br /> > 1. Connecting to multiple databases<br /> ><br /> > The background workers can
apparentlyonly connect to a single database<br /> > at a time, but I want to expose all the databases via the
API.<br/><br /> bgworkers are assigned a database at launch time (if SPI is enabled),<br /> and this database may not
changeduring the worker's lifetime, same as a<br /> normal backend.<br /><br /> Sometimes frustrating, but that's how
itis.<br /><br /> > I think I could use libpq to connect to PostgreSQL on localhost but this<br /> > might have
weirdside-effects in terms of authentication, pid use, stuff<br /> > like that.<br /><br /> If you're going to do
that,why use a bgworker at all?<br /><br /> In general, what do you gain from trying to do this within the database<br
/>server its self, not as an app in front of the DB?<br /><br /> > I could probably manage a pool of dynamic workers
(asof 9.4), one per<br /> > user/database combination or something along those lines.  Even one per<br /> >
request? Is there some kind of IPC system in place to help shuttle the<br /> > requests and responses between
dynamicworkers?  Or do I need to come up<br /> > with my own?<br /><br /> The dynamic shmem code apparently has some
queuingfunctionality. I<br /> haven't used it yet.<br /><br /> > It seems like PostgreSQL itself has a way to
shuttlerequests out to<br /> > workers, is it possible to tap into that system instead?  Basically some<br /> >
wayto send the requests to a PostgreSQL backend from the background worker?<br /><br /> It does?<br /><br /> It's not
theSPI, that executes work directly within the bgworker,<br /> making it behave like a normal backend for the purpose
ofquery execution.<br /><br /> > Or perhaps I shouldn't do this as a worker but rather modify PostgreSQL<br /> >
itselfand do it in a more integrated/destructive manner?<br /><br /> Or just write a front-end.<br /><br /> The problem
you'dhave attempting to modify PostgreSQL its self for this<br /> is that connection dispatch occurs via the
postmaster,which is a<br /> single-threaded process that already needs to do a bit of work to keep<br /> an eye on how
thingsare running. You don't want it constantly busy<br /> processing and dispatching millions of tiny HTTP requests.
Itcan't just<br /> hand a connection off to a back-end immediately after accepting it,<br /> either; it'd have to read
theHTTP headers to determine what database to<br /> connect to. Then launch a new backend for the connection, which
is<br/> horribly inefficient when doing tiny short-lived connections. The<br /> postmaster has no concept of a pool of
backends(unfortunately, IMO) to<br /> re-use.<br /><br /> I imagine (it's not something I've investigated, really) that
you'dwant<br /> a connection accepter process that watched the listening http request<br /> socket. It'd hand
connectionsoff to dispatcher processes that read the<br /> message content to get the target DB and dispatch the
requestto a<br /> worker backend for the appropriate user/db combo, then collect the<br /> results and return them on
theconnection. Hopefully at this point<br /> you're thinking "that sounds a lot like a connection pool"... because
it<br/> is. An awfully complicated one, probably, as you'd have to manage<br /> everything using shared memory segments
andlatches.<br /><br /> In my view it's unwise to try to do this in the DB with PostgreSQL's<br /> architecture. Hack
PgBounceror PgPool to do what you want. Or write a<br /> server with Tomcat/Jetty using JAX-RS and PgJDBC and the built
in<br/> connection pool facilities - you won't *believe* how easy it is.<br /><br /> > 3. Parallelism<br /> ><br
/>> The regular PostgreSQL server can run many queries in parallel<br /><br /> Well, one PostgreSQL instance
(postmaster)may have many backends, each<br /> of which may run queries in series but not in parallel. Any given<br />
processmay only run one query at once.<br /><br /> > but it<br /> > seems like if I am using SPI I could only run
onequery at a time - it's<br /> > not an asynchronous API.<br /><br /> Correct.<br /><br /> > Any help, sage
advice,tips, and suggestions how to move forward in<br /> > these areas would be muchly appreciated!<br /><br />
Don'tdo it with bgworkers.<br /><br /> --<br />  Craig Ringer                   <a href="http://www.2ndQuadrant.com/"
target="_blank">http://www.2ndQuadrant.com/</a><br/>  PostgreSQL Development, 24x7 Support, Training & Services<br
/></blockquote></div>