> I understand that:> 1) you like to use postgres as a "bus" to transfer > messages between connected> clients;> 2) only one database server is > concerned (no redundancy at all);> 3) it is the client code (perl, php ...) > that send the notification (ie,> notifications are not sent by triggers for > example)> > May be you could dedicate one of your database to do this; all > clients could> listen messages on this database and some client code would > decode the payload> and does its job.> > Sylvain
> As to how to start writing an extension:> > > https://www.postgresql.org/docs/9.5/static/extend-extensions.html> > As to > how NOTIFY/LISTEN works:> > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/async.c;h=beef574076c257db0a868b39a946565185f6c53e;hb=ba37ac217791dfdf2b327c4b75e7083b6b03a2f5> > > > I am not so sure another extension is needed. Would it not make more > sense> just to use the dblink extension?> > https://www.postgresql.org/docs/9.5/static/contrib-dblink-function.html> > > Just make a table that contains the names of the other databases and> > connection info.> Then the same trigger that calls the function for NOTIFY > or pg_notify(text,> text) could just as easily call a function with dblink > that determines which> database needs the notify and raises it there.> > -- > > Adrian Klaver> adrian.kla...@aklaver.com Thanks Adrian, that's great. I think I'll be giving the dblink / FDW a shot. Thedocumentation says that FDW is a newer more standard-compliant way to make theconnection, so I thought I give that one a try first. But then I can not find away to call a function / stored procedure from within the foreign database. Isthis possible or FDW is only for tables? I mean how to should I be sending thenotification after these steps: CREATE DATABASE "CentralDb";CREATE USER "notify_only" WITH PASSWORD '123';GRANT ALL PRIVILEGES ON DATABASE "CentralDb" to "notify_only";CREATE FUNCTION notify_hq(text channel, text payload) RETURNS void LANGUAGE plpgsql VOLATILE COST 1 AS $$BEGIN PERFORME pg_notify(channel, payload);END;$$; CREATE DATABASE "LocalDb";CREATE USER "local_user" WITH PASSWORD '321';GRANT ALL PRIVILEGES ON DATABASE "LocalDb" to "local_user"; CREATE EXTENSION postgres_fdw; CREATE SERVER notify_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'CentralDb', port '5432'); CREATE USER MAPPING FOR local_user SERVER notify_server OPTIONS (user 'notify_only', password '123'); At this point I'm stuck, because the next step would be CREATE FOREIGN TABLE butI'm not after a foreign table and a foreign function! How can I call thepg_notify / notify_hq from the LocalDb? Regards,Mehran
