Re: Re: [GENERAL] A simple extension immitating pg_notify‏

On 07/25/2016 08:34 AM, Mehran Ziadloo wrote:
>> 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
>
>
> Here's my use case:
>
> I'm trying to come up with structure-wise homogeneous databases within one
> instance where users can instantiate a new empty database as they
> request for
> their own organization access (a one to one relationship between
> organizations
> and such databases).

To be clear in a Postgres database cluster(what you call an instance I
believe) the individual databases share some global information, for
instance roles. Also a user can at least in psql \l(ist) the other
databases in the cluster. Not sure how clean a divide you want between
organizations.

>
> At the same time, I'm going to use NOTIFY to send out data changes to some
> external application but I don't want to make a new connection for each new
> organization as it poses a waste of system resources and also an
> implementation
> challenge. Instead, I thought it would make more sense to have one central
> database and push all the notifications to there. And from there to the
> external
> application (just like the bus that you've mentioned). Now all I need is one
> listener no matter how many databases I have.

So is the external application global or is it specific to each
organization?

>
> The thing is notification might come at any time in any form. They might
> even be
> sent within a trigger. There're no limitations there. And one more thing, I
> think one listener per PostgreSQL instance would be enough. I'll just
> include
> the sender's info in the notification's payload to know who has
> generated the
> message.
>
> Regards,
> Mehran

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com