Re: HTTP Frontend? (and a brief thought on materialized views)

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