Thread: Use LISTEN/NOTIFY between different databases

On 05/07/12 1:13 PM, Igor wrote:
>
> 1.Is this possible in future releases ?
> 2. How I can organize interconnectbetween two clients connected to
> different DB? I need call NOTIFY from pgsql function in one database
> to another LISTEN client in another database.


a listener isn't 'in a database', its a client application thats polling
for the notifications from the database its connected to.

so in that context, your question doesn't really make sense.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On 05/07/12 1:40 PM, Igor wrote:
>
>
> 2012/5/7 John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com>>
>
>     On 05/07/12 1:13 PM, Igor wrote:
>
>
>         1.Is this possible in future releases ?
>         2. How I can organize interconnectbetween two clients
>         connected to different DB? I need call NOTIFY from pgsql
>         function in one database to another LISTEN client in another
>         database.
>
>
>
>     a listener isn't 'in a database', its a client application thats
>     polling for the notifications from the database its connected to.
>
>
> 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.

then maybe you could have a process to do this work which connects to
all the databases and issues the LISTENs and initiates whatever actions
you need in the other database(s) ?


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Yes, but it is workaround. The native way - is the "communication" on
> DB-backend level.
> It would be very good feature - possibility to set a destination DB in
> NOTIFY command as optional argument.
>
> Is the developers read this list ? :)

Yes, but I find this a very unlikely feature, for a few reasons. First,
there is a completely acceptable "workaround", as you call it. Second, you
are the first person in my recollection to ask for this, so there is
obviously not a high demand. Third, anything that goes cross-database
is subject to lots of very careful care and handling, and I doubt adding
that complexity is worth the small benefit gained.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201205081059
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk+pNOwACgkQvJuQZxSWSsjtdACggxjMfNxJxdfiY2ElxrWyx4E7
E/sAoLnDvepVy6QqhVicLf67kmSB6IqV
=oy5d
-----END PGP SIGNATURE-----

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