Thread: Foreign Tables

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