> 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@aklaver.com
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