Thread: Database transaction related

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

at 4, a socket connection is opened to a unix tools server, that
altogether a different machine.
some unix tools are run in the unix tools machine, an ODBC connection
is opened back to the database server (where 1-4 took place) and
result of running the tools are stored in various tables in the
database.

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?

My assumption was yes because unix tools server was invoked from the
client socket thats the part of the database transaction. Don't know
if my assumption was correct.

One more thing that I would like to mention here is that as soon as
the unix tools server is done with its tool running job, it never
writes back to the client socket. Communication from Unix tools server
to Database server is done using ODBC connection and not the socket
connection.

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.

So I'm just wondering and confused about the whole transaction
behaviour in such a scenario

Any kind of help would be highly appreciated.

Thanks
~Jas

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@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Jasbinder Singh Bali wrote:

> could you please elaborate this concept of queue table?

The basic idea is to for the insert trigger to not explicitly kick
off the work you need to have done, but simply insert a row into a
"work request table".  A separate process notices that there is a
work request, and performs the work.  This can be done by a periodic
cron job, or using NOTIFY/LISTEN.

There was a fairly detailed discussion of this last month - the
thread starts here:

   http://archives.postgresql.org/pgsql-general/2007-04/msg01152.php

- John D. Burger
   MITRE