Re: [GENERAL] Foreign Tables

[Eliot Gable]

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

Re: [GENERAL] Foreign Tables

[Adam Cornett]

On Thu, Nov 17, 2011 at 2:59 AM, Raghavendra <
raghavendra@enterprisedb.com> wrote:

>
>   Shigeru Hanada shigeru.han...@gmail.com
>  7:48 AM (5 hours ago)
> to Eliot, pgsql-general
>   This message may not have been sent by: shigeru.han...@gmail.com  Learn
> more
>   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 
> wrote:
>
>> Hi Eliot,
>>
>> 2011/11/17 Eliot Gable :
>> 
>> > 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

Re: [GENERAL] Foreign Tables

[Raghavendra]

  Shigeru Hanada shigeru.han...@gmail.com
 7:48 AM (5 hours ago)
to Eliot, pgsql-general
  This message may not have been sent by: shigeru.han...@gmail.com  Learn
more
  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 wrote:

> Hi Eliot,
>
> 2011/11/17 Eliot Gable :
> 
> > 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
>

Re: [GENERAL] Foreign Tables

[Shigeru Hanada]

Hi Eliot,

2011/11/17 Eliot Gable :

> 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

Re: [GENERAL] Foreign Tables

[Guillaume Lelarge]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Foreign Tables

[Eliot Gable]

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?

1b) If it does show in server1.foo, I assume it would also fire any
triggers on server1.foo; correct?

2) Given the example in question #1, can I put a trigger on server2.bar and
have it actually fire when server1.foo has an insert, update, or delete
operation on it?

Thanks in advance for any answers.


-- 
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

Re: [GENERAL] Foreign tables using postgresql_fdw or odbc_fdw

[Adrian Klaver]

On Sunday, September 18, 2011 6:32:43 am Edson Carlos Ericksson Richter wrote:
> Tried both, but they don't work for 9.1.
> 
> I want to thank Adrian, without his help I would never find the link that
> confirmed the postgresql_fdw is not working.
> 
> Would be nice to make all this information clear in Foreign Data Wrapper
> documentation at PostgreSQL manuals.

To be fair FDW is a brand new feature on a just released for production version 
of Postgres.  As good as the Postgres docs are, they often take some time to 
catch up with all the ins and outs of the new bright and shiny features.

> 
> I wasted at least 12 hours trying to make it work. The only real option is
> file_fdw, that is not that useful (I would connect my database to files
> just for curiosity - otherwise, I would use only an application that
> validates the file before importing into database for fast operations).

Google is your friend:) It took me about 5 minutes using this search string-
'postgres dblink fdw' to find the link I posted previously. 
As to options take a look at:
http://pgxn.org/tag/fdw/

> 
> odbc_fdw would work, but it's near to impossible for a normal human bean to
> compile using MSVC; and it's not available in binary format (see, I'm not a
> C developer for many, many years now... and I work in an hybrid environment
> with Windows and Linux machines).

Development of Postgres and its associated software tends to happen on Unixen 
machines, so there somewhat of a lack of info on doing development under 
Windows.  I personally have not tried to compile under Windows. From what I 
have 
read though, one heavily promoted option is to use MingW(http://www.mingw.org/) 
as the development environment in Windows.

> 
> Sorry for the rant... I hope to save time for others who wish to use FDW in
> a real scenario.

Another way to save someone time is to use MingW or MSVC to compile an 
extension 
and report back to the community your successes or failures.  There are a lot 
of 
bright energetic people on this list who would be willing and able to help you 
in your endeavor. The knowledge gained would further the cause and make life 
easier on down the road for everyone.

> 
> 
> Regards,
> 
> Edson Richter.


Thanks,
-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Foreign tables using postgresql_fdw or odbc_fdw

[Edson Carlos Ericksson Richter]

Tried both, but they don't work for 9.1.

I want to thank Adrian, without his help I would never find the link that
confirmed the postgresql_fdw is not working.

Would be nice to make all this information clear in Foreign Data Wrapper
documentation at PostgreSQL manuals.

I wasted at least 12 hours trying to make it work. The only real option is
file_fdw, that is not that useful (I would connect my database to files just
for curiosity - otherwise, I would use only an application that validates
the file before importing into database for fast operations).

odbc_fdw would work, but it's near to impossible for a normal human bean to
compile using MSVC; and it's not available in binary format (see, I'm not a
C developer for many, many years now... and I work in an hybrid environment
with Windows and Linux machines).

Sorry for the rant... I hope to save time for others who wish to use FDW in
a real scenario.


Regards,

Edson Richter.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general