Re: Foreign Tables - Mailing list pgsql-general

Thank you for your response...

 

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.