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
2016-07-24 15:55 GMT+02:00 Melvin Davidson <melvin6...@gmail.com>:
>
>
> On Sun, Jul 24, 2016 at 9:35 AM, Adrian Klaver <adrian.kla...@aklaver.com>
> wrote:
>
>> On 07/24/2016 03:31 AM, Mehran Ziadloo wrote:
>>
>>> I'm sorry, you are right. Let me rephrase that.
>>>
>>> What I meant was that the notifications are received by the client
>>> connections
>>> as if it was generated within their corresponding database (let's name
>>> the
>>> database with a client connection listening to it; DB_C), then a
>>> notification
>>> generated by a NOTIFY command within DB_X like this:
>>>
>>> pg_notify_db('DB_C', some_channel, 'payload');
>>>
>>> can be heard by the client. Of course, the client needs to know which
>>> database
>>> has sent the notification but I think we can skip that part since we can
>>> easily
>>> include database name in the notification's payload if necessary.
>>>
>>> That was one of the two solutions. The other one is like this:
>>>
>>> pg_notify_all(some_channel, 'payload');
>>>
>>> And this one sends the notification to all of the client connections,
>>> regardless of which database they are connected/listening to.
>>>
>>> I hope it makes sense now.
>>>
>>>
>>>
>> 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
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> 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.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
