Bret Schuhmacher <bret@thelastmilellc.com> writes:
> What's the best way to invoke a process on another server from a PG
> trigger or procedure? I was thinking of using pl/java to invoke a web
> service on the other box... Can pl/tcl run Expect scripts?
No, but pl/tclu could.
> Or I could use XMLBlaster to send a message to the
> other box to start the other process, but that's an asynchronous call
> and I can't be sure if the remote procedure ran properly.
You've almost figured out the big problem with anything like this;
the trouble spot is the other way around. What if you launch some
remote operation, and it succeeds, and then later your own transaction
rolls back for some unrelated reason? Action FOO did happen in the
external world, but there is no change in the state of the database
--- which at the minimum probably means you'll try to do FOO again
later. Lather, rinse, repeat.
The general conclusion among folks who have actually done this is that
launching external actions from inside a transaction isn't robust.
Instead, have the transaction put an entry into a "to-do queue" table
that is monitored by some client process. You still have to be careful
about failure conditions, but there's a whole lot more flexibility when
it's being driven from a client that's outside the database. See
previous discussions in the archives.
regards, tom lane