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

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) ?

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 ? :)

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

--
Regards

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