Re: Using Postgresql as application server

On Mon, Aug 15, 2011 at 7:06 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
> On 15/08/2011 10:36 PM, Merlin Moncure wrote:
>>
>> On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
>> <andreak(at)officenet(dot)no>  wrote:
>>>
>>> No, PG has never, and will never, act as an application-server.
>>
>> Why in the world not?
>
> The biggest reason is safety. Beyond that, the lack of autonomous
> transactions, stored procedures, in-DB timers, and support for any protocol
> other than the native Pg database query protocol mean it's also rather
> impractical.

well, autonomous transactions can be kludged. beyond that though, I
agree on the sproc point but not the protocol. it's pretty trivial to
frontend the database with a http understanding server which converts
that to libpq representation (node.js gets the nod from that point of
view).

> I guess theoretically one could embed a JVM / Python instance / whatever in
> the postmaster and have it spawn new backends for incoming connections with
> other protocols. But ... why? Why add all that complexity and - more
> importantly - contaiminate PostgreSQL's address space with more code that
> can fail when you don't have to? PLs and user C procedures are already risk
> enough as far as I'm concerned.

I don't see what's so difficult about putting thin protocol server in
front of the database. In terms of security, good coding practices in
languages like plpgsql are well understood and there is no reason to
believe they are any more or less secure than the middleware as long
as good coding practices are followed..

> I *like* the DB being standalone. It's safer from bad code scribbling
> garbage across the heap and corrupting shared memory.

> What do you gain by trying to make Pg play appserver too?

Simple. You eliminate the layer that traditionally sucks down most of
the coding effort and presents most of the bugs. SQL errors and
(especially) transaction state are first class, so that you can just
let the sql engine clean everything up for you when you have an error.
If your application is small enough so that you don't have to break
application serving into a separate machine, you can eliminate moving
data through the protocol and all the application managed caches that
plague traditional stacks. You get to code in a more declarative
functional environment that has (in my humble experience) absolutely
proven itself in terms of defect rates. You get to reduce your LOC by
50-80%.

There are downsides too -- you lose access to the excellent middleware
tools out there, and you are 'stuck' on postgres and need to come up
with hard to find and expensive postgres talent. You need to be
prepared to blaze a path, etc etc.

merlin