Thread: Suggestions for Remote Procedure Calls from PG, please?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 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? That'd be an option, too. 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. Does anyone else invoke a process on a remote server? How do you do it? Thanks, Bret -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (MingW32) iD8DBQFHFsVCIeMC5lK637kRAvBvAKCRAgAg3H7jK/efm8KBlUKUifKV0ACgmo07 3eLZT6pB2XI8uTS47fdYcSw= =rXIJ -----END PGP SIGNATURE-----
Bret, I had quick and robust success using pl/python and pyro. Pyro is Python Remote object. All mentioned modules are BSD-like in license. Harald > 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... > Does anyone else invoke a process on a remote server? How do you do it? -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: > > > 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. > . Thanks for the reply, Tom. I was thinking I could have my remote process send a message back to PG via XMLBlaster, too. XMLBlaster is a MOM-like message-queuing app that guarantees delivery to subscribers. (www.xmlblaster.org). The problem, as you stated, though, is transactional integrity :-(. Hmmm, I'll see about the to-do queue idea. Thanks again for your time! Bret -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (MingW32) iD8DBQFHFtagIeMC5lK637kRAg56AJsF6eNlQWPdpjb8ufiO+xRqZTXymgCfdJFG 4igU9pCasxaVSGOxC0DBbHg= =qKK2 -----END PGP SIGNATURE-----
am Wed, dem 17.10.2007, um 22:30:26 -0400 mailte Bret Schuhmacher folgendes: > Does anyone else invoke a process on a remote server? How do you do it? You can use any untrusted programming language like pl/perlU or plsh. Other solution: use LISTEN/NOTIFY, see http://www.postgresql.org/docs/8.2/interactive/sql-notify.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
> 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? That'd be > an option, too. 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. > > Does anyone else invoke a process on a remote server? How do you do it? > pl/perl samples: http://www.pgsql.cz/index.php/PL/Perlu_-_Untrusted_Perl_%28en%29
Em Thursday 18 October 2007 01:44:33 Bret Schuhmacher escreveu:
>
> Thanks for the reply, Tom. I was thinking I could have my remote
> process send a message back to PG via XMLBlaster, too. XMLBlaster is
> a MOM-like message-queuing app that guarantees delivery to
> subscribers. (www.xmlblaster.org). The problem, as you stated,
> though, is transactional integrity :-(. Hmmm, I'll see about the
> to-do queue idea.
You can try implementing a queue, Bret. Make it a FIFO queue and poll from it
regularly. Ten make your transaction insert a record on that queue and take
your action based on that.
A table as simple as:
id SERIAL, -- you can have a routine to reset this when empty
table TEXT, -- includes schema
primary_key TEXT -- to allow for numeric and text PKs
would allow you to retrieve the row that has been changed and take your action
based on that.
--
Jorge Godoy <jgodoy@gmail.com>