Tiger,
Thanks alot for sharing that info.
Thats exactly how we have implemented our system after seeing the mismatch
in the transactions
for any rollback transactions.
Regards,
~Jas
On 8/29/06, Tiger Quimpo <bopolissimus(dot)lists(at)gmail(dot)com> wrote:
>
> [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
>
>