Re: Using Postgresql as application server

On 08/16/2011 03:06 AM, Craig Ringer 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.
>
> 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.
>

As I've mentioned in other posts, we are using postgresql as a hybrid
application server and have found it to be very practical and easy to
code. For functions that need to run on a regular basis, we use a cron
job, though from what I understand pgAgent should be able to handle that
just as well. Instead of using Listen/Notify to tell an application to
send e-mails, we have an email function written in plpython that sends
the email directly.
The reason to do this is because it gives you "write once" code which
makes your application pretty much client-agnostic.

PG already spawns a new backend for each connection. So your specialized
code has little chance of hosing the whole database.

We connect to other databases when we need to, mostly mysql using the
MySQLdb python module, to move data to and from our website.

Scalability would be increased with multi-master replication, which
bucardo currently does (I haven't tested it yet).

One issue you have is using database style hardware for application
server needs, which may be more expensive.