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
