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

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