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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I'm sorry, you are right. Let me rephrase that.
What I meant was that the notifications are received by the client connectionsas if it was generated within their corresponding database (let's name thedatabase 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 databasehas sent the notification but I think we can 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/listening to.
I hope it makes sense now.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Jul 24, 2016 at 9:35 AM, Adrian Klaver <adrian.klaver@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.klaver@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.

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 <melvin6925@gmail.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 corresponding database (let's name thedatabase 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 databasehas sent the notification but I think we can 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/listening to.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general