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

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.