Обсуждение: 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 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
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
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.
> 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.
And I'm stuck here! Can someone please help me find the problem? Thanks.
Regards,
Mehran
Re: [GENERAL] RE: [GENERAL] Re: [GENERAL] A simple extension immitating pg_notify
От
Alban Hertroys
Дата:
> 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'setc. 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 multipleclusters. Same if you distribute those servers over several hosts, what you seem to think a cluster means. That isthe 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.
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