Search Postgresql Archives

Re: [GENERAL] A simple extension immitating pg_notify‏

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux