Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
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 <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
-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- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
Bret Schuhmacher <[EMAIL PROTECTED]> 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
Re: [GENERAL] 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! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 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- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings