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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
