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]https://sourceforge.net/projects/interdbconnect/,
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]http://archives.postgresql.org/pgsql-hackers/2011-10/msg01329.php.

[1]: https://sourceforge.net/projects/interdbconnect/
[2]: http://archives.postgresql.org/pgsql-hackers/2011-10/msg01329.php

Regards,
--
Shigeru Hanada

Shigeru Hanada shigeru.hanada@gmail.com
7:48 AM (5 hours ago)
to Eliot, pgsql-general
This message may not have been sent by: shigeru.hanada@gmail.com Learn
more<http://mail.google.com/support/bin/answer.py?hl=en&amp;ctx=mail&amp;answer=185812&gt;
Report phishing
Why this message is popping up in my inbox ?
Is there any problem with in-house gmail setting of mine.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Thu, Nov 17, 2011 at 7:48 AM, Shigeru Hanada <shigeru.hanada@gmail.com>wrote:

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-general
This message may not have been sent by: shigeru.hanada@gmail.com Learn
more<http://mail.google.com/support/bin/answer.py?hl=en&amp;ctx=mail&amp;answer=185812&gt;
Report phishing
Why this message is popping up in my inbox ?
Is there any problem with in-house gmail setting of mine.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

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

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

--
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. 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?)

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