Bret Schuhmacher <bret@xxxxxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
