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

On Fri, Mar 30, 2012 at 3:57 AM, Daniel Farina <daniel(at)heroku(dot)com> wrote:

> On Thu, Mar 29, 2012 at 8:04 AM, Andrew Dunstan <andrew(at)dunslane(dot)net>
> wrote:
>

> Lastly, a case that can not as easily be fixed without some more
> thinking is leveraging caching semantics of HTTP. think people would
> really, really like that, if they could get away from having to
> hand-roll their own cache regeneration in common cases.
>

I think this could be an interesting possibility. I wonder if the cost of
a round-trip makes the cost of sending the actual data (vs a 304 response)
irrelevant - as long as PostgreSQL is caching effectively internally it's
possible it can send back the actual content as fast as it can calculate
the ETag for it, so doing an extra query to check for changes could
possibly slow things down, or at least eliminate the benefit. Probably
worth trying, though.

Supporting this "automagically" would require some kind of generic
algorithm for calculating the Last-Modifed time or ETag for a given query.
As a default we may be able to just fall back on some internal global
value that is guaranteed to change if the database has changed (I think the
WAL files have some kind of serial number system we might use) so at the
very least you could send back a 304 Not Modified if literally nothing in
the database has changed. Narrowing that down to specific table timestamps
might be possible, too, for simple queries. It depends what data is
already available, I wouldn't want to add any extra book keeping for it.

A more pragmatic may be to have the HTTP request include SQL code to
generate an ETag or Last-Modified value to test with; the app could run
that first and it would be used for caching. Something like
calcLastModified=max(modified_date) on the query string.