Re: Foreign Tables

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