suggestions for integrating PostgreSQL Listen/Notify

[Arturo Perez <pe...@gsicommerce.com>]

Hi all,

The PostgreSQL database has a notification system built-in so that one 
database-connected app can signal another.  The mechanism is called 
Listen/Notify.  Some JDBC-centric docs here: 
http://jdbc.postgresql.org/documentation/81/listennotify.html

How would I go about integrating that into Cayenne for certain types of 
cache invalidations?  I have several apps only some of which are 
Cayenne-based so using JavaGroups and JMS for this sort of thing isn't 
straightforward.

Is there any documentation on Cayenne's cross-vm cache synchronization 
protocols?

tia,
arturo

Re: suggestions for integrating PostgreSQL Listen/Notify

[Andrus Adamchik <an...@objectstyle.org>]

While there are many java-to-java messaging solutions (and all of them can be used without Cayenne), this PG mechanism is pretty cool, as "NOTIFY mymessage" can be sent from places like SQL scripts, etc. So yeah, it does make sense to look at Cayenne integration.

So... per http://cayenne.apache.org/doc/caching-and-fresh-data.html one of the key aspects of the current cache is a separation between the object cache and query cache. We have a plan to unify them at some point, probably in 3.2, but now they are separate caches, and are managed differently. In fact only query cache is really "manageable", so let's focus on the query cache. 

Query cache is represented by DI-injectable (talking about Cayenne 3.1) org.apache.cayenne.cache.QueryCache instance. It defines general cache management methods needed for Cayenne to use the cache. The one that you are interested in is "removeGroup". 

Your message "protocol" may consist of just a cache group name String. This is very simple (you don't have to send object snapshots or anything) and can be done even from a SQL script. Now in Cayenne you can decorate whatever QueryCache implementation you are using. The decorator would (1) fire a PG event when Cayenne calls "removeGroup", and (2) would run a listener in a separate thread, which itself would call "removeGroup" when a PG event arrives. 

Pretty simple in theory :-)

Andrus




On Oct 14, 2011, at 5:50 AM, Arturo Perez wrote:
> Hi all,
> 
> The PostgreSQL database has a notification system built-in so that one 
> database-connected app can signal another.  The mechanism is called 
> Listen/Notify.  Some JDBC-centric docs here: 
> http://jdbc.postgresql.org/documentation/81/listennotify.html
> 
> How would I go about integrating that into Cayenne for certain types of 
> cache invalidations?  I have several apps only some of which are 
> Cayenne-based so using JavaGroups and JMS for this sort of thing isn't 
> straightforward.
> 
> Is there any documentation on Cayenne's cross-vm cache synchronization 
> protocols?
> 
> tia,
> arturo
> 
>