Thread: A simple extension immitating pg_notify

On 7/23/2016 8:24 PM, Mehran Ziadloo wrote:
> I'm looking for a way to send notifications from within one database to
> another. As if pg_notify accepted a database name, or even to all of the
> databases. So far I could not find a way to do so. Then someone told
> me that I
> need to implement this feature myself and add it PostgreSQL as an
> extension.
>
> Being new to extension development, I have no idea where to start. Can
> someone
> please guide me to the right direction? I'm sure implementing such a
> function
> is pretty simple but only if you know where to start, which I don't!
>

how would a 'database' receive a notification?   notifications are sent
from SQL running on the database server to a listening client app.



--
john r pierce, recycling bits in santa cruz

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

Mehran Ziadloo <mehran20@hotmail.com> writes:
> What I meant was that the notifications are received by the client connectionsas if it was generated within their
correspondingdatabase (let's name thedatabase with a client connection listening to it; DB_C), then a notification
generatedby 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 databasehas sent the notification but I think
wecan skip that part since we can easilyinclude 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/listeningto. 

I do not think the first one would be too hard (look in commands/async.c),
but the second one would be problematic, because of the assumption that
NOTIFY message contents are expressed in the relevant database's encoding.
In the first case you could put it on the head of the sending backend to
convert to the target DB's encoding --- and, if that conversion failed,
it could error out cleanly: no message sent, no transaction committed.
In the second case you would have to make it the responsibility of
receiving backends to do the encoding conversions, and AFAICS it'd be
impossible to have clean semantics for failures: the sending transaction
would have committed all right, but some of the recipients wouldn't get
the notification.

            regards, tom lane

Melvin Davidson <melvin6925@gmail.com> writes:
> I am not so sure another extension is needed. Would it not make more sense
> just to use the dblink extension?

That would be a quick-n-dirty fix, but it would give up all the guarantees
NOTIFY provides about messages being sent if and only if the sending
transaction commits.

            regards, tom lane