Re: Foreign Tables

Hi Eliot,

2011/11/17 Eliot Gable <egable+pgsql-general(at)gmail(dot)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