Thanks for the reply. I had a follow up question w.r.t listen/notify:
I am planning to associate a NOTIFY with an update on a table - a trigger is associated with the update, and we execute NOTIFY in the trigger code. The NOTIFY directly goes to a remote server and contains the updated row data in the payload (serialized). Each update will result in a diffferent payload (timestamp will be in it). My question is whether LISTEN/NOTIFY was designed to handle this scenario? The update rate may scale to very high levels, and each of those updates will do a trigger call and issue a notification. Also, the size of the payload may be large (maybe 10KB per notification).
I am planning to do a load test myself - but it would be great if you already know some good reason not to go ahead with this idea.
Looking at the big picture, my main requirement is to communicate with a remote server about an update ASAP - its part of a very time critical workflow. I am new to Postgre (and SQL servers in general) and don't know what the best solution is. I was in Google earlier, where bigtables are used everywhere. And bigtables have a fantastic framework for asynchronous execution of code based on update on any cell of the table. I am trying to find something similar here, which really SCALES.
Thanks,
Aman
On Sun, Dec 25, 2011 at 3:02 AM, Alban Hertroys <haramrae@xxxxxxxxx> wrote:
That would be using LISTEN/NOTIFY.On 23 Dec 2011, at 14:33, Aman Gupta wrote:
> The problem statement is mentioned here:
> http://stackoverflow.com/questions/8615408/postgresql-triggers-defining-a-global-resource-java
>
> I am looking for the "best" solution to that problem.
If you perform RPCs directly from within your trigger, the transaction needs to wait until the RPC call succeeded, keeping locks open much longer than necessary. That will block other transactions from touching these rows while the RPC is going on, among which will be autovacuum.
For an implementation using LISTEN/NOTIFY you'd basically write a local daemon that's polling the database with LISTEN and performs the necessary RPC when needed. It doesn't even need to be written in Java, you could use a language that can handle NOTIFY as an event (although in the end it probably boils down to the same, but closer to kernel-level).
If your PG is pre-9, then you'll want some mechanism that keeps a pool of pending data for RPC. In 9.0 and up you can send record information along with NOTIFY.
Alban Hertroys
--
The scale of a problem often equals the size of an ego.
