On 6/1/2010 11:12 AM, Szymon Guz wrote: > > > 2010/6/1 Sim Zacks <sim@xxxxxxxxxxxxxx <mailto:sim@xxxxxxxxxxxxxx>> > > 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. > I am actually using a number of methods. Triggers are only used when the function does have to be completed as part of the transaction or it is considered an error. Also a big advantage of the plpythonu is that you can use try..except blocks so that if something fails you can process the failure and still allow the transaction to complete. For all other functions, such as email and FTP I am using either queues or the Listen/Notify mechanism. The queues run in a cron job that call a database function to complete the task. I have a database function called SendQueuedEmails which runs a loop on the emaildetails table and sends each email one by one. Any errors are written to the errors table and a "bounce" email is sent to the user. I use Listen/Notify for what I call "asynchronous triggers". Something that I want to happen immediately upon a specific transaction, but I don't want to wait for the result and the transaction is not dependent on. Errors are written to an error table so I can review them later and a "bounce" email is sent when relevant. I just prefer to have all the functionality in the database, so I have a single location for all server code and a single standard method of calling those functions. Sim -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
