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

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(at)gmail(dot)com> wrote:

> 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, 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.
>
>
>