Re: Use LISTEN/NOTIFY between different databases

On Mon, May 7, 2012 at 11:52 PM, John R Pierce <pierce@hogranch.com> wrote:
>> 2012/5/7 John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com>>
>>    On 05/07/12 1:13 PM, Igor wrote:
>> I understand this. But I need "link" between two clients, connected to
>> different databases.
>>
>> Its real situation. We use sharded cluster based on plproxy. The calling
>> function for DB queries is equal in all databases (see plproxy
>> documentation). Now we need to release some internal logic between clients
>> based on certain events which occurred in this functions. The LISTEN/NOTIFY
>> mechanism is very good feature, but event occur NOT in connected DB - i.e.
>> in destination DB via plproxy wrapper. I.e. wee need subject.


1) Install PL/Proxy functions into shard.
+ Synchronous - remote call is launched instantly
- Messy - creates complex dependencies between dbs.
- Non-transactional (remote call may commit, but local call may not)
- Not good idea if high transaction rate is expected
  (Adds network latency to each call, many new connections)

2) Use PgQ ( http://wiki.postgresql.org/wiki/PGQ_Tutorial )
+ Good for high loads as it does batch-processing
+ Transactional
- Asynchronous

--
marko