RE: [GENERAL] Re: [GENERAL] A simple extension immitating pg_notify‏

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