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