Re: Database transaction related

On Fri, May 11, 2007 at 04:07:10PM -0400, Jasbinder Singh Bali wrote:
> I have a transaction in postgres database as follows:
>
> 1 Insert in table xyz
> 2 trigger fired on table xyz after insert
> 3 Trigger calls a function func
> 4 func is an implementation of a client socket in perl
>
> 1-4 happens in database environment only

<snip>

> Now my question is, what all happens in the unix tools server, is that
> a part of the database transaction that started from step 1 above?

No, how could the database know they are the same transaction? You
would somehow have to get the unix server to send commands via your
socket.

What you're doing is usually the wrong approach. What people usually do
is have the trigger insert a row into a queue table and have the unix
tools server connect and do the work listed in the table, possibly
marking the original row "done" somehow.

> Why I'm concerned about all this is because off late, a database
> insert failed in the unix tool server and the whole transaction start
> from step 1 above was not rolled back. It was still successful till
> step 4.

To make that work the client would have to detect the unix tools server
failed and abort the transaction locally also...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.