Re: plpythonu / using pg as an application server

2010/6/1 Sim Zacks <sim(at)compulab(dot)co(dot)il>

> PG 8.2
>
> I am using plpythonu to add application server functionality to my
> postgresql database.
>
> For example, I have triggers and functions that FTP files, sends email,
> processes files, etc..
>
>
> Is there any good reason not to include this functionality directly in
> the database? (Too much parallel processing, engine not equipped for
> that kind of processing, threading issues...)
>
>
> Thanks
> Sim
>
>
The problem is that such a trigger can last very long and makes some non
transactional operations. When you perform some insert or update, and the
trigger sends an email, the insert/update lasts much longer while blocking
other transactions. As as result the overall database efficiency is much
worse.
Another problem is that sometimes sending an email can fail, should then be
made rollback of the insert/update operation?
I'd rather use some message queue so the trigger just inserts an email info
to a table `emails` instead of sending it. Another trigger would just insert
some information to a table `ftpsites` to indicate some ftp address to
download. There should also be some process at the background that will
select the information from those tables and send emails, process the ftp
sites and so on.

regards
Szymon Guz