Thread: plpythonu / using pg as an application server

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

2010/6/1 Sim Zacks <sim@compulab.co.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...)
>

there are some issues still

* missing integrated scheduler
* missing autonomous transaction

if these isn't issue for you, then you can use "rich" database without
bigger problems.

Regards

Pavel Stehule

>
> Thanks
> Sim
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On 6/1/2010 11:12 AM, Szymon Guz wrote:
>
>
> 2010/6/1 Sim Zacks <sim@compulab.co.il <mailto:sim@compulab.co.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.
>
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

>> 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...)
>>
>>
> there are some issues still
>
> * missing integrated scheduler
> * missing autonomous transaction
>
> if these isn't issue for you, then you can use "rich" database without
> bigger problems.
>
These are very real issues. As workarounds, I am using cron on the
server to call database functions. I am planning on installing pgAgent,
but still haven't gotten around to it.
For autonomous transactions or what we might call asynchronous triggers,
I use the listen/notify mechanism and the server calls the function when
the Notify call is made.
Sim

As an example of a filesystem access that is transaction dependent:
When I create a new supplier in the database, I need a set of
directories built on the file system.
If the directories are not there, it will cause a lot of problems when
dealing with this supplier.
When creating the supplier, I use a trigger to build the directories,
and if it can't then it will error out, roll back the supplier insert
and give an error to the user that they cannot build the directory
structure and to please speak with IT to resolve the issue.

Sim