Re: Shared Objects (Dynamic loading)

[original email got setn to Jasbinder but not to the list
fixing that]
On 8/28/06, Gerald Timothy G Quimpo... wrote:
> Any reason why you don't do your functions and triggers in
> pl/pgsql instead? It's simpler to setup and learn
> than pl/perl, IMO.

On Mon, 2006-08-28 at 03:27 -0400, Jasbinder Bali wrote:
> Its because my trigger has to initiate some unix tools
> and the code for the same is already written in Perl.
> So my trigger just needs to call the Perl program that
> would do the needful eventually.

OK. Something to keep in mind:

Whatever your triggers do can be rolled back if the
transaction rolls back for whatever reason. If
you're calling external programs inside your trigger,
you're performing actions which you can't rollback.
For any rolledback transactions where you already ran
the external unix tools, there's going to be a
disconnect between the data and what the unix tools
did.

One approach to this is to have a status table where
you push things that should be executed externally,
have the triggers insert into that status table, and
have a separate program that either polls the status
table or does a LISTEN for notification that it should
check the status table.

There's some discussion of that in the archives. This
way, inserts into the status table ALSO rollback if the
transaction rolls back, so if a status table entry
ever becomes visible, it's only because the transaction
already completed and it's safe to do the external
unix tool tasks.

tiger