Обсуждение: Foreign Tables
I have read as much as I can find on 9.1's foreign table support, and it looks almost ideal for bridging the gap between all the databases and collecting all the data into a single report. However, I am unclear on a few points...
1a) Can the foreign tables be written to? For example, I have server1 with table foo and server2 which does 'create foreign table bar' where bar references server1.foo. Can server2 write to bar and have it show in server1.foo?
1b) If it does show in server1.foo, I assume it would also fire any triggers on server1.foo; correct?
2) Given the example in question #1, can I put a trigger on server2.bar and have it actually fire when server1.foo has an insert, update, or delete operation on it?
Thanks in advance for any answers.
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
On Wed, 2011-11-16 at 13:38 -0500, Eliot Gable wrote: > I am working on a reporting project where I need to generate a report based > on data from several different network appliances. Each appliance runs a > PostgreSQL database which houses all of the information for the appliance. > Further, there are dozens of triggers in the database which fire when > various tables are touched in various different ways (insert, update, > delete). These triggers currently are used to build materialized views of > much of the data that I want to summarize in the reports. > > I have read as much as I can find on 9.1's foreign table support, and it > looks almost ideal for bridging the gap between all the databases and > collecting all the data into a single report. However, I am unclear on a > few points... > > 1a) Can the foreign tables be written to? For example, I have server1 with > table foo and server2 which does 'create foreign table bar' where bar > references server1.foo. Can server2 write to bar and have it show in > server1.foo? > No, you can't (yet?). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Hi Eliot, 2011/11/17 Eliot Gable <egable+pgsql-general@gmail.com>: <snip> > 1a) Can the foreign tables be written to? For example, I have server1 with > table foo and server2 which does 'create foreign table bar' where bar > references server1.foo. Can server2 write to bar and have it show in > server1.foo? Foreign tables in 9.1 are read-only, so you can't write to them. Making foreign tables writable is a TODO item, but ISTM it's difficult to implement it for even 9.2. So the answer to your question 1a) is "No". BTW, I'm interested in your use case very much because I'm working on enhancement of foreign tables for 9.2. I would appreciate it if you tell me some details of your reporting system. Foreign tables may suit your reporting system. a) Where are materialized views, triggers and source tables? I guess all of them are on appliances, not on PostgreSQL server for reporting. b) Do you need to update data on appliances during making a report? If you do, how do you do it without foreign tables? (from reporting application, or using dblink or something?) If source of report are on appliances as materialized views (or ordinary tables), and you don't need to update data on appliances, I think you can use foreign tables to gather information on a PostgreSQL server. In this case, you need to define foreign tables for each materialized view (or ordinary table). Then, you can execute SELECT statement using foreign tables on the reporting server to gather information from appliances. FDW for PostgreSQL 9.1, pgsql_fdw, is provided as a third party product[1], though it seems not ready for production use. # Currently you need to extract pgsql_fdw from git repository. Incidentally, pgsql_fdw is being proposed as a contrib module of 9.2[2]. [1]https://sourceforge.net/projects/interdbconnect/ [2]http://archives.postgresql.org/pgsql-hackers/2011-10/msg01329.php Regards, -- Shigeru Hanada
![](https://ssl.gstatic.com/ui/v1/icons/mail/profile_mask2.png)
| 7:48 AM (5 hours ago) ![]() | ![]() ![]() | ||
|
![](https://mail.google.com/mail/u/0/images/cleardot.gif)
Why this message is popping up in my inbox ?
Is there any problem with in-house gmail setting of mine.
---
Regards,
Raghavendra
EnterpriseDB Corporation
On Thu, Nov 17, 2011 at 7:48 AM, Shigeru Hanada <shigeru.hanada@gmail.com> wrote:
Hi Eliot,
2011/11/17 Eliot Gable <egable+pgsql-general@gmail.com>:
<snip>> 1a) Can the foreign tables be written to? For example, I have server1 withForeign tables in 9.1 are read-only, so you can't write to them. Making
> table foo and server2 which does 'create foreign table bar' where bar
> references server1.foo. Can server2 write to bar and have it show in
> server1.foo?
foreign tables writable is a TODO item, but ISTM it's difficult to
implement it for even 9.2. So the answer to your question 1a) is "No".
BTW, I'm interested in your use case very much because I'm working on
enhancement of foreign tables for 9.2. I would appreciate it if you tell
me some details of your reporting system. Foreign tables may suit your
reporting system.
a) Where are materialized views, triggers and source tables? I guess all
of them are on appliances, not on PostgreSQL server for reporting.
b) Do you need to update data on appliances during making a report? If you
do, how do you do it without foreign tables? (from reporting application,
or using dblink or something?)
If source of report are on appliances as materialized views (or ordinary
tables), and you don't need to update data on appliances, I think you can
use foreign tables to gather information on a PostgreSQL server. In this
case, you need to define foreign tables for each materialized view (or
ordinary table). Then, you can execute SELECT statement using foreign
tables on the reporting server to gather information from appliances.
FDW for PostgreSQL 9.1, pgsql_fdw, is provided as a third party product[1],
though it seems not ready for production use.
# Currently you need to extract pgsql_fdw from git repository.
Incidentally, pgsql_fdw is being proposed as a contrib module of 9.2[2].
[1]https://sourceforge.net/projects/interdbconnect/
[2]http://archives.postgresql.org/pgsql-hackers/2011-10/msg01329.php
Regards,
--
Shigeru Hanada
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Nov 17, 2011 at 2:59 AM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
Shigeru Hanada shigeru.hanada@gmail.com 7:48 AM (5 hours ago)
to Eliot, pgsql-generalWhy this message is popping up in my inbox ?Is there any problem with in-house gmail setting of mine.---Regards,RaghavendraEnterpriseDB CorporationOn Thu, Nov 17, 2011 at 7:48 AM, Shigeru Hanada <shigeru.hanada@gmail.com> wrote:Hi Eliot,
2011/11/17 Eliot Gable <egable+pgsql-general@gmail.com>:
<snip>> 1a) Can the foreign tables be written to? For example, I have server1 withForeign tables in 9.1 are read-only, so you can't write to them. Making
> table foo and server2 which does 'create foreign table bar' where bar
> references server1.foo. Can server2 write to bar and have it show in
> server1.foo?
foreign tables writable is a TODO item, but ISTM it's difficult to
implement it for even 9.2. So the answer to your question 1a) is "No".
BTW, I'm interested in your use case very much because I'm working on
enhancement of foreign tables for 9.2. I would appreciate it if you tell
me some details of your reporting system. Foreign tables may suit your
reporting system.
a) Where are materialized views, triggers and source tables? I guess all
of them are on appliances, not on PostgreSQL server for reporting.
b) Do you need to update data on appliances during making a report? If you
do, how do you do it without foreign tables? (from reporting application,
or using dblink or something?)
If source of report are on appliances as materialized views (or ordinary
tables), and you don't need to update data on appliances, I think you can
use foreign tables to gather information on a PostgreSQL server. In this
case, you need to define foreign tables for each materialized view (or
ordinary table). Then, you can execute SELECT statement using foreign
tables on the reporting server to gather information from appliances.
FDW for PostgreSQL 9.1, pgsql_fdw, is provided as a third party product[1],
though it seems not ready for production use.
# Currently you need to extract pgsql_fdw from git repository.
Incidentally, pgsql_fdw is being proposed as a contrib module of 9.2[2].
[1]https://sourceforge.net/projects/interdbconnect/
[2]http://archives.postgresql.org/pgsql-hackers/2011-10/msg01329.php
Regards,
--
Shigeru Hanada
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
This message is displayed because Gmail sees the 'from' address as being at gmail.com, but the SMTP headers show that it was actually sent from the mailing list server, so it reports that the message doesn't appear to be from who it says its from. On a technical level, its right, the message didn't come from Gmail and the mailing list software spoofed the from address.
-Adam
Thank you for your response...
Foreign tables in 9.1 are read-only, so you can't write to them. Makingforeign tables writable is a TODO item, but ISTM it's difficult to
implement it for even 9.2. So the answer to your question 1a) is "No".
BTW, I'm interested in your use case very much because I'm working on
enhancement of foreign tables for 9.2. I would appreciate it if you tell
me some details of your reporting system. Foreign tables may suit your
reporting system.
a) Where are materialized views, triggers and source tables? I guess all
of them are on appliances, not on PostgreSQL server for reporting.
b) Do you need to update data on appliances during making a report? If you
do, how do you do it without foreign tables? (from reporting application,
or using dblink or something?)
Each appliance is a self-contained unit, including for reporting purposes. However, there is a way to 'link' the appliances together to make them aware of each other. Basically, a table stores the hostname and IP of other systems. In the interface, someone would go to the 'Reports' tab and they should be able to see the stats for the local appliance, stats for each appliance that this one has been made aware of, and combined stats for all appliances this one has been made aware of. Basically, there are objects which are shared across the appliances which could be under the same logical category. For example, users. Each appliance has its own set of registered users and groups, but you might have the same group name across multiple devices with different users in the group on different devices. So, a combined view would show the stats for that group across all appliances and it would list all users across all appliances that are in that group.
Basically, my idea to do the reports was to put a trigger on the table which stores the hostname and IP and then build the foreign server entities, foreign tables, and triggers on the foreign tables. Ideally, triggers on the foreign tables would fire when something is written to that foreign table. The triggers could then materialize a local copy of the statistics from those foreign tables into a local table. I would have a 'combined' table which is updated when the triggers on the foreign tables fire. And triggers on the local tables would also update the 'combined' stats table. Then, to generate my three types of reports, I would pull from the local tables to display stats for the local system, from each of the foreign tables to display stats for each device this one knows about, and from the 'combined' table which has been getting updated every time the foreign tables are written to and which would hold a composite of the information from the local and all the foreign tables.
I would not need special guarantees that triggers on the local node on the foreign table references complete before the transaction on the foreign system is committed. It would be permissable if the foreign system committed a transaction which updates the table, fires a local trigger, and then the local trigger fails for some reason. I have no need for that to cause a rollback of the transaction on the foreign system. Such an occurrence should be rare, and I would adjust for it by re-materializing the 'combined' view from time-to-time (maybe nightly). The reports do not need to be 100% accurate, just 99% accurate. As long as they are 'eventually' accurate, that is all I care about.
The reports are never really 'generated.' They are real-time. So, the tables would store the exact information which would be displayed as the report. A C trigger is used to communicate changes made to the report tables in real time to a daemon which talks over web sockets to client browsers. The client browsers are then updated in real time by push events from the server as they occur in the report tables. So, there is no actual reporting server in all of this. Clients only ever connect to their local node, but they should be able to see the stats of the local node and any remote systems the local node knows about.
So, how much of this is possible to do now with foreign tables, and how much of it would I have to wait on? If I can do even some of it right now with foreign tables, it would be useful.
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero