> 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).
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.
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