Thread: postgresql triggers - defining a global resource (java)
The problem statement is mentioned here:
http://stackoverflow.com/questions/8615408/postgresql-triggers-defining-a-global-resource-javaI am looking for the "best" solution to that problem.
Thanks,
Aman
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. That would be using LISTEN/NOTIFY. If you perform RPCs directly from within your trigger, the transaction needs to wait until the RPC call succeeded, keepinglocks open much longer than necessary. That will block other transactions from touching these rows while the RPC isgoing 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 andperforms the necessary RPC when needed. It doesn't even need to be written in Java, you could use a language that canhandle 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 sendrecord information along with NOTIFY. Alban Hertroys -- The scale of a problem often equals the size of an ego.
Hey Alban,
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@gmail.com> 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.
On Mon, Dec 26, 2011 at 8:32 AM, Aman Gupta <gupta.aman@gmail.com> wrote: > Hey Alban, > > 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). postgresql notify payload is limited to 8000 bytes. you need to write the payload into a table and have a LISTENer process it in a queue. This type of thing can get real complex real fast, especially if you need to distribute the work to multiple processors. single processor is probably ok with ad hoc implementation. If you are struggling with this (although I'd still give it a go), you may want to check out PGQ (http://wiki.postgresql.org/wiki/PGQ_Tutorial). merlin