Re: HTTP Frontend? (and a brief thought on materialized views) - Mailing list pgsql-hackers

On 03/29/2012 10:37 AM, Dobes Vandermeer wrote:
> Hi guys,
>
> Something from Josh's recent blog post about summer of code clicked 
> with me - the HTTP / SQL concept.
>
> It was something I'd been thinking about earlier, how people really 
> like HTTP APIs and this is one of the drivers behind adoption of  some 
> "NoSQL" databases out there.
>
> Some things that I think are great about NoSQL that are missing in 
> PostgreSQL are:
>
>  1. The "reduce" function which could be modeled as a kind of
>     materialized view with an index on it.  With materialized views
>     and the ability to pull fields from JSON and XML data structures
>     you could easily do a "NoSQL" database inside of PostgreSQL by
>     saving the document as a big string and then using materialized
>     views to have all kinds of derived data from those documents.
>      Sounds cool on paper, anyway.
>  2. HTTP RESTful API.  This is obviously not as useful as a good admin
>     tool like pgAdmin or a fast binary protocol like we have now but
>     it's way more buzzword compliant and would come in handy
>     sometimes.  With CouchDB I was able to allow "public" data in the
>     database to be accessed directly from the browser using JSONP
>     and/or a simple HTTP proxy in the server instead of doing any of
>     that work within the app server.  So, that saves a step somewhere.
>      With some basic support for stored procedures and serving files
>     directly via this HTTP thing you could potentially eliminate the
>     app server for public, read-only parts of a site/application.
>  3. The perception of extremely low latency and/or high throughput -
>     like fetching a row in less than 1ms or whatever.  I don't know
>     the exact numbers I just know they have to be insanely low
>     (latency) or impressively high (throughput).  We could use
>     PostgreSQL as a query cache to accelerate your MySQL!  Something
>     like that :-P.
>  4. Rebelliousness against "the man".  In our case SQL can't be "the
>     man", but we can find something PostgreSQL isn't and position
>     against that.
>
> Anyway, 1&2 seem inspiring to me and I'd love to help out with both. 
>  3&4 seem more like marketing tasks of some sort...
>
> I think I could probably start hacking up an HTTP API of some sort, I 
> wasn't able to log into the PostgreSQL Wiki so I created a page with 
> some notes here:
>
> http://dobesv.com/docs/postgresql-http-api.html
>
> For materialized views I think that can be a bit of a can of worms, 
> especially if you want to do incremental updates of some sort because 
> you have to figure out what rows need to be recalculated when you 
> update a row in a "source" table, and how best to update them.  But if 
> someone thinks they know how I can assist in implementation.
>
> Anyway, I hope I can help AND that I posted this in the correct 
> mailing list!
>


1. I've been in discussion with some people about adding simple JSON 
extract functions. We already have some (i.e. xpath()) for XML.

2. You might find htsql <http://htsql.org/> interesting.


cheers

andrew