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@officenet.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.