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