pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Asynchronous Communication across two independent components 8+ messages / 3 participants [nested] [flat]
* Asynchronous Communication across two independent components @ 2003-03-24 15:35 Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-24 21:27 ` Re: Asynchronous Communication across two independent components Darko Prenosil <darko.prenosil@finteh.hr> 0 siblings, 1 reply; 8+ messages in thread From: Damien Dougan @ 2003-03-24 15:35 UTC (permalink / raw) To: pgsql-interfaces Hi All, I have a problem with how the interface to Postgres works (specifically the libpq) and was wondering if there is a known solution to the type of communication I wish to perform. Basically, I have two components - one which is responsible for submitting calls to stored procedures (the Sender), and one which is responsible for handling the results (the Receiver). (I've done it this way for a number of reasons - based on our platform architecture, the need to avoid tying up threads in our platform, and the need to store binary data in-memory whilst the stored procedure is being executed - this data will not be held on the database during the life of the database operation). Thus my communication problems boil down to having two separate processes - I want the Receiver to be able to deal with the results of stored procedures that the Sender has executed. I looked at the Client Interface functions, and they (PQsendQuery and PQgetResult) seem to require both the Sender and Receiver to be available on the same connection (which I don't want to have to do - since I'd be tying up threads in our platform waiting for PGgetResult to return NULL before I could subtmit another PQsendQuery). So I'm pretty sure this is not what I'm after. I also looked at the LISTEN/NOTIFY, which seemed promising. However, there does not appear to be any mechanism to identify the event that caused the notification. I was hoping to be able to have the Receiver handle the notification by going to the appropriate relation and picking up the data which was now ready for it - but there's no way to know which entry it is! Is there a known Postgres solution to the problem of asynchronous communication with separate sender/receiver? Is there an alternative to this approach (bearing in mind I want to avoid tying up threads in our platform whilst the database is doing its thing - especially when there may be delays due to locks from other requests). The key goal in the design of the solution is request throughput with a limited number of threads - I don't mind the database locking until some requests are continued, but I can't have the thread pool available to my own service being consumed with idle listeners! Many thanks for any advice and pointers, Damien ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Asynchronous Communication across two independent components 2003-03-24 15:35 Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> @ 2003-03-24 21:27 ` Darko Prenosil <darko.prenosil@finteh.hr> 2003-03-25 09:15 ` Re: Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 0 siblings, 1 reply; 8+ messages in thread From: Darko Prenosil @ 2003-03-24 21:27 UTC (permalink / raw) To: Damien Dougan <damien.dougan@mobilecohesion.com>; pgsql-interfaces On Monday 24 March 2003 15:35, Damien Dougan wrote: > Hi All, > > I have a problem with how the interface to Postgres works (specifically the > libpq) and was wondering if there is a known solution to the type of > communication I wish to perform. > > > Basically, I have two components - one which is responsible for submitting > calls to stored procedures (the Sender), and one which is responsible for > handling the results (the Receiver). > > (I've done it this way for a number of reasons - based on our platform > architecture, the need to avoid tying up threads in our platform, and the > need to store binary data in-memory whilst the stored procedure is being > executed - this data will not be held on the database during the life of > the database operation). > > > Thus my communication problems boil down to having two separate processes - > I want the Receiver to be able to deal with the results of stored > procedures that the Sender has executed. > > > I looked at the Client Interface functions, and they (PQsendQuery and > PQgetResult) seem to require both the Sender and Receiver to be available > on the same connection (which I don't want to have to do - since I'd be > tying up threads in our platform waiting for PGgetResult to return NULL > before I could subtmit another PQsendQuery). > > So I'm pretty sure this is not what I'm after. > You can send whole bunch of SQL commands using PQsendQuery, not only one ! After that you can get more than one PGResults depending on what commands you send to Postgres. You only need to terminate it with ";". For example: PQSendQuery("SELECT * FROM pg_table; BEGIN TRANSACTION; DELETE FROM tbl WHERE id = 1; COMMIT;"); When PQSendQuery command is executed, queries are just send to server. Non-blocking means: program will not block and wait for results to come !!! After that you should use combination of PQconsumeInput, PQisBusy and PQgetResult. You can even cancel rest of the queries with PQrequestCancel. It is all explained at: http://developer.postgresql.org/docs/postgres/libpq-async.html , and it works perfectly (spiking from my experience) !!! If You need to wait to see if first command passes, and then send another, threads and non-blocking commands can't help You! Then You simple must execute with PQExec and wait for result ! > > I also looked at the LISTEN/NOTIFY, which seemed promising. However, there > does not appear to be any mechanism to identify the event that caused the > notification. I was hoping to be able to have the Receiver handle the > notification by going to the appropriate relation and picking up the data > which was now ready for it - but there's no way to know which entry it is! > You can RAISE NOTICE inside of the function("stored procedure") with custom text. But You do not need this. PQsendQuery with multiple sql commands is what you need ! > > Is there a known Postgres solution to the problem of asynchronous > communication with separate sender/receiver? > > Is there an alternative to this approach (bearing in mind I want to avoid > tying up threads in our platform whilst the database is doing its thing - > especially when there may be delays due to locks from other requests). The > key goal in the design of the solution is request throughput with a limited > number of threads - I don't mind the database locking until some requests > are continued, but I can't have the thread pool available to my own service > being consumed with idle listeners! > > > Many thanks for any advice and pointers, > > Damien > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Asynchronous Communication across two independent components 2003-03-24 15:35 Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-24 21:27 ` Re: Asynchronous Communication across two independent components Darko Prenosil <darko.prenosil@finteh.hr> @ 2003-03-25 09:15 ` Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-25 13:13 ` Re: Asynchronous Communication across two independent components Darko Prenosil <darko.prenosil@finteh.hr> 0 siblings, 1 reply; 8+ messages in thread From: Damien Dougan @ 2003-03-25 09:15 UTC (permalink / raw) To: Darko Prenosil <darko.prenosil@finteh.hr>; pgsql-interfaces Darko, Thanks for the response, but I'm not sure if your suggestions will meet my needs - as the page you referenced seems to suggest that these operations must all go over the same connection (and I have a Sender and a Receiver on separate connections). Some comments embedded below ... On Monday 24 March 2003 9:27 pm, you wrote: > You can send whole bunch of SQL commands using PQsendQuery, not only one ! > After that you can get more than one PGResults depending on what commands > you send to Postgres. You only need to terminate it with ";". > For example: > PQSendQuery("SELECT * FROM pg_table; BEGIN TRANSACTION; DELETE FROM tbl > WHERE id = 1; COMMIT;"); > When PQSendQuery command is executed, queries are just send to server. > Non-blocking means: program will not block and wait for results to come !!! > After that you should use combination of PQconsumeInput, PQisBusy and > PQgetResult. You can even cancel rest of the queries with PQrequestCancel. > It is all explained at: > http://developer.postgresql.org/docs/postgres/libpq-async.html , and it > works perfectly (spiking from my experience) !!! I did have a read through this before posting - and I was hoping the above mechanisms would do the trick for me - but there appears to be two problems with the PQsendQuery and PQgetResult. (1) You cannot call PQsendQuery on the same connection until PQgetResult has returned null. (2) You cannot call PQgetResult on a different connection from PQsendQuery to obtain the results of a query. So whilst your solution would be fine for asynchronous comms within a single connection, I'm afraid its not what I'm looking for! > If You need to wait to see if first command passes, and then send another, > threads and non-blocking commands can't help You! Then You simple must > execute with PQExec and wait for result ! > > > I also looked at the LISTEN/NOTIFY, which seemed promising. However, > > there does not appear to be any mechanism to identify the event that > > caused the notification. I was hoping to be able to have the Receiver > > handle the notification by going to the appropriate relation and picking > > up the data which was now ready for it - but there's no way to know which > > entry it is! > > You can RAISE NOTICE inside of the function("stored procedure") with > custom text. But You do not need this. PQsendQuery with multiple sql > commands is what you need ! > Thanks for the RAISE NOTICE heads-up - I'll take a look at this. Damien ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Asynchronous Communication across two independent components 2003-03-24 15:35 Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-24 21:27 ` Re: Asynchronous Communication across two independent components Darko Prenosil <darko.prenosil@finteh.hr> 2003-03-25 09:15 ` Re: Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> @ 2003-03-25 13:13 ` Darko Prenosil <darko.prenosil@finteh.hr> 2003-03-25 12:23 ` Re: Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 0 siblings, 1 reply; 8+ messages in thread From: Darko Prenosil @ 2003-03-25 13:13 UTC (permalink / raw) To: Damien Dougan <damien.dougan@mobilecohesion.com>; pgsql-interfaces On Tuesday 25 March 2003 09:15, Damien Dougan wrote: > Darko, > > Thanks for the response, but I'm not sure if your suggestions will meet my > needs - as the page you referenced seems to suggest that these operations > must all go over the same connection (and I have a Sender and a Receiver on > separate connections). > How about sharing connections between sender and receiver. After all PGConnection is only pointer that can be same for Sender and Receiver. How do you mean to connect Sender and Receiver ? How should postgres know which Sender will post results to which Receiver ? The only way to have sender and receiver on different connections is to write some kind of own server that will connect server and receiver to "Pair", but I did newer heard for such "construction" in SQL server philosophy. It is also possible to store the "results" from Sender to tables, and Receiver can periodically check if something happened and get the results! It all seems a little fuzzy to me. Maybe You can explain it with more details. > Some comments embedded below ... > > On Monday 24 March 2003 9:27 pm, you wrote: > > You can send whole bunch of SQL commands using PQsendQuery, not only one > > ! After that you can get more than one PGResults depending on what > > commands you send to Postgres. You only need to terminate it with ";". > > For example: > > PQSendQuery("SELECT * FROM pg_table; BEGIN TRANSACTION; DELETE FROM tbl > > WHERE id = 1; COMMIT;"); > > When PQSendQuery command is executed, queries are just send to server. > > Non-blocking means: program will not block and wait for results to come > > !!! After that you should use combination of PQconsumeInput, PQisBusy and > > PQgetResult. You can even cancel rest of the queries with > > PQrequestCancel. It is all explained at: > > http://developer.postgresql.org/docs/postgres/libpq-async.html , and it > > works perfectly (spiking from my experience) !!! > > I did have a read through this before posting - and I was hoping the above > mechanisms would do the trick for me - but there appears to be two problems > with the PQsendQuery and PQgetResult. > > (1) You cannot call PQsendQuery on the same connection until PQgetResult > has returned null. That is true, but why do you wish to send another query if you send them all in one PQSendQuery ? > > (2) You cannot call PQgetResult on a different connection from PQsendQuery > to obtain the results of a query. > > So whilst your solution would be fine for asynchronous comms within a > single connection, I'm afraid its not what I'm looking for! > > > If You need to wait to see if first command passes, and then send > > another, threads and non-blocking commands can't help You! Then You > > simple must execute with PQExec and wait for result ! > > > > > I also looked at the LISTEN/NOTIFY, which seemed promising. However, > > > there does not appear to be any mechanism to identify the event that > > > caused the notification. I was hoping to be able to have the Receiver > > > handle the notification by going to the appropriate relation and > > > picking up the data which was now ready for it - but there's no way to > > > know which entry it is! > > > > You can RAISE NOTICE inside of the function("stored procedure") with > > custom text. But You do not need this. PQsendQuery with multiple sql > > commands is what you need ! > > Thanks for the RAISE NOTICE heads-up - I'll take a look at this. > Unfortunately RAISE NOTICE can only function through same connection, and if You insist on different connection, this will not help. > Damien > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Asynchronous Communication across two independent components 2003-03-24 15:35 Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-24 21:27 ` Re: Asynchronous Communication across two independent components Darko Prenosil <darko.prenosil@finteh.hr> 2003-03-25 09:15 ` Re: Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-25 13:13 ` Re: Asynchronous Communication across two independent components Darko Prenosil <darko.prenosil@finteh.hr> @ 2003-03-25 12:23 ` Damien Dougan <damien.dougan@mobilecohesion.com> 0 siblings, 0 replies; 8+ messages in thread From: Damien Dougan @ 2003-03-25 12:23 UTC (permalink / raw) To: Darko Prenosil <darko.prenosil@finteh.hr>; pgsql-interfaces On Tuesday 25 March 2003 1:13 pm, Darko Prenosil wrote: > How about sharing connections between sender and receiver. After all > PGConnection is only pointer that can be same for Sender and Receiver. > How do you mean to connect Sender and Receiver ? How should postgres know > which Sender will post results to which Receiver ? I understand why the PQsendQuery and PQgetResult won't work across connections, so that's not an issue. > The only way to have sender and receiver on different connections is to > write some kind of own server that will connect server and receiver to > "Pair", but I did newer heard for such "construction" in SQL server > philosophy. > > It is also possible to store the "results" from Sender to tables, and > Receiver can periodically check if something happened and get the results! Yes - that's what I intend doing with the LISTEN and NOTIFY - I will have a Trigger StoredProc that will be called when a table is updated in response to the Sender issuing a request. The Trigger StoredProc will then insert an entry in another table that will result in a notification. I've separated out the logic of insertion into the notification table into a Trigger StoredProc because its not really part of the logic of updating our schema tables - effectively the notification table is meta-data to help the Receiver determine what has been changed. I just think this is a little clunky and was hoping for something cleaner ... > That is true, but why do you wish to send another query if you send them > all in one PQSendQuery ? Our architecture is message based, so I can't delay the Sender waiting for incoming messages (buffering them together) into a single PQsendQuery because its all asynchronous communication. > Unfortunately RAISE NOTICE can only function through same connection, and > if You insist on different connection, this will not help. Again, thanks for the heads-up on this! Damien ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Asynchronous Communication across two independent @ 2003-03-25 13:41 Christoph Haller <ch@rodos.fzk.de> 2003-03-25 14:14 ` Re: Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 0 siblings, 1 reply; 8+ messages in thread From: Christoph Haller @ 2003-03-25 13:41 UTC (permalink / raw) To: pgsql-interfaces; +Cc: damien.dougan@mobilecohesion.com Just out of curiosity. It may look like a bit of a hack, but as long as it is clearly documented, it seems doable: > > > How about sharing connections between sender and receiver. After all > > PGConnection is only pointer that can be same for Sender and Receiver. Yes, share connection by a common PGconn data set, write/read via a binary file or whatever interprocess data exchange you prefer. > > How do you mean to connect Sender and Receiver ? How should postgres know > > which Sender will post results to which Receiver ? That's not the point as I understand it. There is one sender using PQsendQuery, and one receiver using PQgetResult. So postgres does not even need to know there are two processes working as sender resp. receiver. > > Our architecture is message based, so I can't delay the Sender waiting for > incoming messages (buffering them together) into a single PQsendQuery because > its all asynchronous communication. > Really? Before making a call to PQsendQuery you have to make sure the previous results have been completely processed by PQgetResult. So you need some kind of interprocess communication facility anyway to tell the sender when another PQsendQuery is possible. And as long as there is no ok from the receiver, the sender has to accumulate the queries. Does this sound plausible or did I get something completely wrong? Regards, Christoph ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Asynchronous Communication across two independent components 2003-03-25 13:41 Re: Asynchronous Communication across two independent Christoph Haller <ch@rodos.fzk.de> @ 2003-03-25 14:14 ` Damien Dougan <damien.dougan@mobilecohesion.com> 0 siblings, 0 replies; 8+ messages in thread From: Damien Dougan @ 2003-03-25 14:14 UTC (permalink / raw) To: Christoph Haller <ch@rodos.fzk.de>; pgsql-interfaces On Tuesday 25 March 2003 1:41 pm, Christoph Haller wrote: > Yes, share connection by a common PGconn data set, write/read via a > binary file > or whatever interprocess data exchange you prefer. But doesn't this approach limit the number of database operations to the number of threads in our platform? If I have a connection shared between a Sender and a Receiver, I have two threads waiting on a single database operation to complete (since I cannot issue another PQsendQuery over that connection until the PQgetQuery returns NULL). This is something I want to avoid - I want a completely detached asynchronous communication model. A Sender fires messages into the database and doesn't care for the results - when they happen, what they are. A Receiver waits for notifications and doesn't care how they happened - how long ago the Sender sent them etc. Thus I can have two threads in my system (one Sender, one Receiver) that can pump messages in/out of the database as fast as they can receive them. The database can worry about locks - its internal architecture should be well designed to cope with that - my business logic shouldn't have to worry about it. > Really? Before making a call to PQsendQuery you have to make sure the > previous > results have been completely processed by PQgetResult. So you need some > kind of > interprocess communication facility anyway to tell the sender when > another > PQsendQuery is possible. And as long as there is no ok from the > receiver, the > sender has to accumulate the queries. > > Does this sound plausible or did I get something completely wrong? My intention is to have the Sender make a synchronous stored procedure call, which will then make an asynchronous stored procedure call, before immediately returning. Thus as far as the Sender is concerned, it is making requests which are near instantanious. The asynchronous stored procedure will do its thing (possibly after some delay if there are locks in place), and trigger a notification to the Receiver, which can then get the results (via meta-data identifying the changed rows in the table) and deal with them. Thus, unless I've missed something (please shout if I have!), I don't have to deal with asynchronous comms via API. The clunk is that I need meta-data and an intermediatory stored procedure before and after table processing. Before table processing to turn the synchronous Sender request into an asynchronous logic request. After table processing to populate the meta-data so the logic in the asynchronous request is decoupled from the notification parts (which are nothing to do with the business logic). Damien ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Asynchronous Communication across two independent @ 2003-03-25 14:44 Christoph Haller <ch@rodos.fzk.de> 0 siblings, 0 replies; 8+ messages in thread From: Christoph Haller @ 2003-03-25 14:44 UTC (permalink / raw) To: pgsql-interfaces; +Cc: damien.dougan@mobilecohesion.com > > On Tuesday 25 March 2003 1:41 pm, Christoph Haller wrote: > > > Yes, share connection by a common PGconn data set, write/read via a > > binary file > > or whatever interprocess data exchange you prefer. > > But doesn't this approach limit the number of database operations to the=20 > number of threads in our platform? No, I don't think so. > > If I have a connection shared between a Sender and a Receiver, I have two= > =20 > threads waiting on a single database operation to complete (since I canno= > t=20 > issue another PQsendQuery over that connection until the PQgetQuery retur= > ns=20 > NULL). But they behave as one thread, and yes, another PQsendQuery has to wait until PQgetQuery returns NULL. > > This is something I want to avoid - I want a completely detached asynchro= > nous=20 > communication model. A Sender fires messages into the database and doesn'= > t=20 > care for the results - when they happen, what they are. A Receiver waits = > for=20 > notifications and doesn't care how they happened - how long ago the Sende= > r=20 > sent them etc. > > Thus I can have two threads in my system (one Sender, one Receiver) that = > can=20 > pump messages in/out of the database as fast as they can receive them. Th= > e=20 > database can worry about locks - its internal architecture should be well= > =20 > designed to cope with that - my business logic shouldn't have to worry ab= > out=20 > it. This is what I thought you're going to implement on your own. I've never heard of any pump mechanism as you describe it as a pre-defined feature. So if you write two C programs acting as sender/receiver daemons in the background, from a black box point of view, they would offer exactly this functionality. I would be interested in hearing about platforms which offer that by default. Regards, Christoph ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-03-25 14:44 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-03-24 15:35 Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-24 21:27 ` Darko Prenosil <darko.prenosil@finteh.hr> 2003-03-25 09:15 ` Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-25 13:13 ` Darko Prenosil <darko.prenosil@finteh.hr> 2003-03-25 12:23 ` Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-25 13:41 Re: Asynchronous Communication across two independent Christoph Haller <ch@rodos.fzk.de> 2003-03-25 14:14 ` Re: Asynchronous Communication across two independent components Damien Dougan <damien.dougan@mobilecohesion.com> 2003-03-25 14:44 Re: Asynchronous Communication across two independent Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox