Re: [GENERAL] A simple extension immitating pg_notify‏

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 oneinstance where users can instantiate a new empty database as they request fortheir own organization access (a one to one relationship between organizationsand such databases).
At the same time, I'm going to use NOTIFY to send out data changes to someexternal application but I don't want to make a new connection for each neworganization as it poses a waste of system resources and also an implementationchallenge. Instead, I thought it would make more sense to have one centraldatabase and push all the notifications to there. And from there to the externalapplication (just like the bus that you've mentioned). Now all I need is onelistener no matter how many databases I have.
The thing is notification might come at any time in any form. They might even besent within a trigger. There're no limitations there. And one more thing, Ithink one listener per PostgreSQL instance would be enough. I'll just includethe sender's info in the notification's payload to know who has generated themessage.
Regards,Mehran

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

Sorry if my terminology is not accurate. But by an instance, I mean a PostgreSQLinstallation. And I call it an instance (and not a database) not to confuse itwith the concept of databases (as in databases / schemas). Even when I'm tryingto clarify the terminology, it's hard due to lack of distinguishable words!
And here, I'm not talking about the cluster version of PostgreSQL. Simple, oldfashion PostgreSQL will do.

Adrian said:> So is the external application global or is it specific to each > organization?

First off, maybe I shouldn't have brought up the concept of organizations as itwill sidetrack the discussion. It's just a domain entity. But just to answeryour question; there will be one application for each PostgreSQL instance,listening to whatever it has to say. And as we have already established, eachinstance is consisted of multiple (logical) databases, which each DB serves adifferent group of users (A.K.A. an organization). So an application will bereceiving notifications from different (logical) databases through one singleconnection to a central database in the instance. Even though I haven't thoughtof it yet, but it is safe to consider that each application is in charge of oneinstance only (there might be more than one instance but I'm getting ahead ofmyself here).
Now let's get back to the problem at hand. I've decided to give the postgres_fdwa try. And this is how far I've managed to go:
$ psql -hlocalhost -Upostgres -W
=# CREATE DATABASE central;=# \c central=# CREATE FUNCTION "public"."notify" (IN channel text, IN payload text)=# RETURNS void-# LANGUAGE plpgsql-# VOLATILE -# CALLED ON NULL INPUT-# SECURITY INVOKER-# COST 1-# AS $$$# BEGIN$# PERFORM pg_notify(channel, payload);$# END;$# $$;
=# CREATE USER notify_only WITH PASSWORD '123';=# GRANT USAGE ON SCHEMA "public" to notify_only;=# \q
Just a test:
$ psql -hlocalhost -Unotify_only -dcentral -W=# SELECT "public".notify('ch', 'Hi there');=# \q
And it works for me. Now let's create the rest of the objects:
$ psql -hlocalhost -Upostgres -W
=# CREATE DATABSE org1;=# CREATE USER org1_user WITH PASSWORD '234';=# GRANT ALL PRIVILEGES ON DATABASE "org1" TO "org1_user";=# \c org1=# CREATE EXTENSION postgres_fdw;=# CREATE SERVER central_database FOREIGN DATA WRAPPER postgres_fdw-# OPTIONS (host 'localhost', dbname 'central', port '5432');=# CREATE USER MAPPING FOR org1_user-# SERVER central_database -# OPTIONS (user 'notify_only', password '123');=# CREATE FOREIGN TABLE "public".notify_hq()-# SERVER central_database-# OPTIONS (schema_name 'public', table_name 'notify');=#\q
$ psql -hlocalhost -Uorg1_user -dorg1 -W
=# SELECT notify_hq('channel', 'From org1 to headquarter');ERROR: function notify_hq(unknown, unknown) does not existLINE 1: SELECT notify_hq('channel', 'From org1 to headquarter'); ^HINT: No function matches the given name and argument types. You might need toadd explicit type casts.

And I'm stuck here! Can someone please help me find the problem? Thanks.
Regards,Mehran

On 26 Jul 2016, at 2:52, Mehran Ziadloo <mehran20@hotmail.com> wrote:

Sorry if my terminology is not accurate. But by an instance, I mean a PostgreSQL
installation. And I call it an instance (and not a database) not to confuse it
with the concept of databases (as in databases / schemas). Even when I'm trying
to clarify the terminology, it's hard due to lack of distinguishable words!

And here, I'm not talking about the cluster version of PostgreSQL. Simple, old
fashion PostgreSQL will do.

Nobody here is talking about some clustered version of PG.

What you call an 'instance' (or 'installation') is called a cluster. A cluster contains databases, which contain schema's etc. In database terms, a cluster is a single database server, a single 'installation' in your terms.

If you would install multiple PG servers in separate directories, running on separate port numbers, you would have multiple clusters. Same if you distribute those servers over several hosts, what you seem to think a cluster means. That is the difference between a cluster of databases and a cluster of servers.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

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

On 07/25/2016 05:52 PM, Mehran Ziadloo wrote:

Sorry if my terminology is not accurate. But by an instance, I mean a
PostgreSQL
installation. And I call it an instance (and not a database) not to
confuse it
with the concept of databases (as in databases / schemas). Even when I'm
trying
to clarify the terminology, it's hard due to lack of distinguishable words!

And here, I'm not talking about the cluster version of PostgreSQL.
Simple, old
fashion PostgreSQL will do.

See Albans's post.

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

First off, maybe I shouldn't have brought up the concept of
organizations as it
will sidetrack the discussion. It's just a domain entity. But just to answer
your question; there will be one application for each PostgreSQL instance,
listening to whatever it has to say. And as we have already established,
each
instance is consisted of multiple (logical) databases, which each DB
serves a
different group of users (A.K.A. an organization). So an application will be
receiving notifications from different (logical) databases through one
single
connection to a central database in the instance. Even though I haven't
thought
of it yet, but it is safe to consider that each application is in charge
of one
instance only (there might be more than one instance but I'm getting
ahead of
myself here).

Now let's get back to the problem at hand. I've decided to give the
postgres_fdw
a try. And this is how far I've managed to go:

$ psql -hlocalhost -Upostgres -W

=# CREATE DATABASE central;
=# \c central
=# CREATE FUNCTION "public"."notify" (IN channel text, IN payload text)
=# RETURNS void
-# LANGUAGE plpgsql
-# VOLATILE
-# CALLED ON NULL INPUT
-# SECURITY INVOKER
-# COST 1
-# AS $$
$# BEGIN
$# PERFORM pg_notify(channel, payload);
$# END;
$# $$;

=# CREATE USER notify_only WITH PASSWORD '123';
=# GRANT USAGE ON SCHEMA "public" to notify_only;
=# \q

Just a test:

$ psql -hlocalhost -Unotify_only -dcentral -W
=# SELECT "public".notify('ch', 'Hi there');
=# \q

And it works for me. Now let's create the rest of the objects:

$ psql -hlocalhost -Upostgres -W

=# CREATE DATABSE org1;
=# CREATE USER org1_user WITH PASSWORD '234';
=# GRANT ALL PRIVILEGES ON DATABASE "org1" TO "org1_user";
=# \c org1
=# CREATE EXTENSION postgres_fdw;
=# CREATE SERVER central_database FOREIGN DATA WRAPPER postgres_fdw
-# OPTIONS (host 'localhost', dbname 'central', port '5432');
=# CREATE USER MAPPING FOR org1_user
-# SERVER central_database
-# OPTIONS (user 'notify_only', password '123');
=# CREATE FOREIGN TABLE "public".notify_hq()
-# SERVER central_database
-# OPTIONS (schema_name 'public', table_name 'notify');
=#\q

$ psql -hlocalhost -Uorg1_user -dorg1 -W

=# SELECT notify_hq('channel', 'From org1 to headquarter');
ERROR: function notify_hq(unknown, unknown) does not exist
LINE 1: SELECT notify_hq('channel', 'From org1 to headquarter');
^
HINT: No function matches the given name and argument types. You might
need to
add explicit type casts.

Well the above is saying that notify_hq as a function does not exist,
which is true as it is a foreign table. AFAIK, the postgres_fdw can only
work with tables. If you want to run non-table commands you will need to
look at dblink:

https://www.postgresql.org/docs/9.5/static/dblink.html

And I'm stuck here! Can someone please help me find the problem? Thanks.

Regards,
Mehran

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