Re: postgresql triggers - defining a global resource (java)

On Mon, Dec 26, 2011 at 8:32 AM, Aman Gupta <gupta(dot)aman(at)gmail(dot)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