Re: Tips/advice for implementing integrated RESTful HTTP API

On 09/02/2014 12:50 AM, Dobes Vandermeer wrote:
> Hmm yes I am learning that the BG worker system isn't as helpful as I
> had hoped due to the single database restriction.
> 
> As for a writing a frontend this might be the best solution.
> 
> A java frontend would be easy but pointless because the whole point here
> is to provide a lightweight access method to the database for
> environments that don't have the ability to use the jdbc or libpq
> libraries.  Deploying a java setup would be too much trouble.

If you can't run libpq, you can't run *anything* really, it's very
lightweight. I think you misunderstood what I was saying; I'm talking
about it acting as a proxy for HTTP-based requests, running on or in
front of the PostgreSQL server like a server-side connection pool would.

You can have:

[client machine]    [    PostgreSQL server machine       ]
client --- HTTP --- Tomcat/JAX-RS  -- pgjdbc -- PostgreSQL

and despite what people often say, a .war deployed on jetty or tomcat
isn't exactly heavyweight.

Same idea as PgBouncer or PgPool. The advantage over hacking
PgBouncer/PgPool for the job is that Tomcat can already do a lot of what
you want using built-in, pre-existing functionality. Connection pool
management, low level REST-style HTTP processing, JSON handling etc are
all done for you.

> 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
> requests using sockets wouldn't be any better than connecting back using
> libpq.

Yep.

> A C frontend using libevent would be easy enough to make and deploy for
> this I guess.
> 
> But... Maybe nobody really wants this thing anyway, there seem to be
> some other options out there already.

It's something I think would be interesting to have, but IMO to be
really useful it'd need to support composing object graphs as json, a
json query format, etc. So you can say "get me this customer with all
their addresses and contact records" without having to issue a bunch of
queries (round trips) or use ORM-style left-join-and-deduplicate hacks
that waste bandwidth and are messy and annoying.

Just sending chunks of SQL and getting back arrays of tuples isn't very
interesting or, IMO, hugely useful for the kind of apps that want to
avoid JDBC/libpq. Especially webapps.

Close care to security and auth would also need to be taken. You don't
want to be sending a username/password with each request; you need a
reasonable authentication token system, request signing to prevent
replay attacks, idempotent requests, etc.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services