Re: A simple extension immitating pg_notify

On Sun, Jul 24, 2016 at 9:35 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.