RE: [GENERAL] Re: [GENERAL] A simple extension immitating pg_notify
| От | Mehran Ziadloo |
|---|---|
| Тема | RE: [GENERAL] Re: [GENERAL] A simple extension immitating pg_notify |
| Дата | |
| Msg-id | DUB121-W8B21ECE6201E4A72E5CBCD00E0@phx.gbl обсуждение исходный текст |
| Ответ на | Re: Re: [GENERAL] A simple extension immitating pg_notify (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Ответы |
Re: [GENERAL] RE: [GENERAL] Re: [GENERAL] A simple extension immitating pg_notify
(Alban Hertroys <haramrae@gmail.com>)
Re: RE: [GENERAL] Re: [GENERAL] A simple extension immitating pg_notify (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: Adrian KlaverДата:
Сообщение: Re: Re: [GENERAL] A simple extension immitating pg_notify