Suggestions for Remote Procedure Calls from PG, please?

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>