|
> 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.klaver@xxxxxxxxxxx Thanks Adrian, that's great. I think I'll be giving the dblink / FDW a shot. The documentation says that FDW is a newer more standard-compliant way to make the connection, so I thought I give that one a try first. But then I can not find a way to call a function / stored procedure from within the foreign database. Is this possible or FDW is only for tables? I mean how to should I be sending the notification 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 but I'm not after a foreign table and a foreign function! How can I call the pg_notify / notify_hq from the LocalDb? Regards, Mehran |
