Re: Procedural Languages

On Thu, May 31, 2012 at 2:22 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Thu, 2012-05-31 at 11:23 -0700, Darren Duncan wrote:
>> Michael Nolan wrote:
>> > PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily
>> > to launch shell scripts from triggers, for example to update an external
>> > website when a row in a table has been inserted, deleted or updated.
>>
>> There is also another way to do what you describe that might be more secure.
>>
>> Rather than having the DBMS launch shell scripts directly, instead use
>> LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an
>> ordinary client script listening for them, and the client script launches the
>> shell scripts when it gets a message.
>>
>> This way, you need a persistent client script, but you don't need to invoke the
>> shell in the DBMS ... or use the untrusted version of PL/Perl if that's all it
>> was for.
>
> An additional advantage is that if you issue NOTIFY with exactly the
> same message many times in one transaction, the LISTENer only gets the
> message once.
>
The big one though is that the notifications are only sent on commit.
This being said while this is a very important advantage it is a
limitation and I can see corner cases (logging custom info even when
transactions rollback) that might be better served by untrusted
language stored procs.

Best Wishes,
Chris Travers