Thread: Feature proposal: www_fdw
Greetings postgres hackers! Here you can find www_fdw feature documentation: http://wiki.postgresql.org/wiki/WWW_FDW Looking forward for your feedback. -- Alexander Soudakov Developer Programmer email: cygakoB@gmail.com skype: asudakov
On Wed, Sep 28, 2011 at 05:32:37PM +0400, Alexander Soudakov wrote: > Greetings postgres hackers! > > Here you can find www_fdw feature documentation: > http://wiki.postgresql.org/wiki/WWW_FDW > > Looking forward for your feedback. Do you have some libraries you plan to base this on, or will you be hand-tooling it all? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On 09/28/2011 09:32 AM, Alexander Soudakov wrote: > Greetings postgres hackers! > > Here you can find www_fdw feature documentation: > http://wiki.postgresql.org/wiki/WWW_FDW > > Looking forward for your feedback. > Why should this be a core feature, as the subject suggests? It could just be an extension, like other FDWs, no? cheers andrew
On Wed, Sep 28, 2011 at 7:17 PM, David Fetter <david@fetter.org> wrote: > On Wed, Sep 28, 2011 at 05:32:37PM +0400, Alexander Soudakov wrote: >> Greetings postgres hackers! >> >> Here you can find www_fdw feature documentation: >> http://wiki.postgresql.org/wiki/WWW_FDW >> >> Looking forward for your feedback. > > Do you have some libraries you plan to base this on, or will you be > hand-tooling it all? I was planning to use 3rd party libraries for json/xml/yaml, interactions with services. The same way as FWD from here http://wiki.postgresql.org/wiki/Foreign_data_wrappers do. But now I guess it might be a problem to include it under contrib directory (same way as file_fdw), right? > > Cheers, > David. > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- Alexander Soudakov Developer Programmer email: cygakoB@gmail.com google talk: cygakoB@gmail.com jabber: asudakov@jabber.org icq uin: 311516511 skype: asudakov
On 09/28/2011 11:41 AM, Alexander Soudakov wrote: > On Wed, Sep 28, 2011 at 7:17 PM, David Fetter<david@fetter.org> wrote: >> On Wed, Sep 28, 2011 at 05:32:37PM +0400, Alexander Soudakov wrote: >>> Greetings postgres hackers! >>> >>> Here you can find www_fdw feature documentation: >>> http://wiki.postgresql.org/wiki/WWW_FDW >>> >>> Looking forward for your feedback. >> Do you have some libraries you plan to base this on, or will you be >> hand-tooling it all? > I was planning to use 3rd party libraries for json/xml/yaml, > interactions with services. The same way as FWD from here > http://wiki.postgresql.org/wiki/Foreign_data_wrappers do. > > But now I guess it might be a problem to include it under contrib > directory (same way as file_fdw), right? > Postgres is designed to be extensible. Many people are creating FDWs which are being published elsewhere, like <http://pgxn.org/>. There is no need to put them all in contrib or anywhere else in the core code. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Why should this be a core feature, as the subject suggests? It could
> just be an extension, like other FDWs, no?
In fact it had *better* be an extension, not core, because anything that
allows the server to go out and touch the web is going to be a security
hazard in some people's usages. I can see that some people will want
this type of functionality, but others definitely won't.
regards, tom lane
Guys,
I suggest Alexander to announce his project just to let all us know and
avoid duplicate work. I hope it's a good starter project for Alexander !
I agree with Andrew, it's also should be posted to -general.
It's clear it should be an extension !
Oleg
On Wed, 28 Sep 2011, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> Why should this be a core feature, as the subject suggests? It could
>> just be an extension, like other FDWs, no?
>
> In fact it had *better* be an extension, not core, because anything that
> allows the server to go out and touch the web is going to be a security
> hazard in some people's usages. I can see that some people will want
> this type of functionality, but others definitely won't.
>
> regards, tom lane
>
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
On Sep28, 2011, at 15:32 , Alexander Soudakov wrote: > Here you can find www_fdw feature documentation: > http://wiki.postgresql.org/wiki/WWW_FDW Certainly looks useful (as a third-party extension, as others have already pointed out) What I didn't quite understand is how one would pass (dynamic) parameters for a GET request. For example, not too long agoI needed to access the Google Maps API from postgres. I ended up using pl/python, and now wonder if your FDW would supportthat use-case. best regards, Florian Pflug
Florian Pflug <fgp@phlo.org> wrote: > On Sep28, 2011, at 15:32 , Alexander Soudakov wrote: >> Here you can find www_fdw feature documentation: >> http://wiki.postgresql.org/wiki/WWW_FDW > > Certainly looks useful (as a third-party extension, as others have > already pointed out) Our programmers agree that it is likely to be useful here. I agree that it should be an extension. > What I didn't quite understand is how one would pass (dynamic) > parameters for a GET request. For example, not too long ago I > needed to access the Google Maps API from postgres. I ended up > using pl/python, and now wonder if your FDW would support that > use-case. I would assume that the usual ? to start parameters and & between parameters would be used. For example, with Google Maps: http://maps.google.com/maps?hl=en&ie=UTF8&hq=&hnear=Madison,+Dane,+Wisconsin&ll=43.074684,-89.38188&spn=0.003006,0.00383&t=h&z=18&layer=c&cbll=43.07468,-89.381742&panoid=LhJ-PFHVzxRguJ6h616mmQ&cbp=12,355.53,,0,-1.32 -Kevin
On Thu, Sep 29, 2011 at 1:20 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Florian Pflug <fgp@phlo.org> wrote: >> On Sep28, 2011, at 15:32 , Alexander Soudakov wrote: >>> Here you can find www_fdw feature documentation: >>> http://wiki.postgresql.org/wiki/WWW_FDW >> >> Certainly looks useful (as a third-party extension, as others have >> already pointed out) > > Our programmers agree that it is likely to be useful here. I agree > that it should be an extension. > >> What I didn't quite understand is how one would pass (dynamic) >> parameters for a GET request. For example, not too long ago I >> needed to access the Google Maps API from postgres. I ended up >> using pl/python, and now wonder if your FDW would support that >> use-case. > > I would assume that the usual ? to start parameters and & between > parameters would be used. For example, with Google Maps: > > http://maps.google.com/maps?hl=en&ie=UTF8&hq=&hnear=Madison,+Dane,+Wisconsin&ll=43.074684,-89.38188&spn=0.003006,0.00383&t=h&z=18&layer=c&cbll=43.07468,-89.381742&panoid=LhJ-PFHVzxRguJ6h616mmQ&cbp=12,355.53,,0,-1.32 > > -Kevin > There would be an option to specify callback for forming request (request_serialize_callback): it would be passed with configuration parameters, details of the query and action (currently it's only SELECT). So it can: * add specific parameters (like developer key) * change column name to query parameter name (for eg: column name - "column", query parameter - "q") * create dynamic parameter And return query string via output parameter "uri". Also I plan to add output parameter "request" of composite type for future to use it for passing any post parameters (or some other http headers specific to api). -- Alexander Soudakov Developer Programmer email: cygakoB@gmail.com skype: asudakov
2011/9/28 Florian Pflug <fgp@phlo.org>: > On Sep28, 2011, at 15:32 , Alexander Soudakov wrote: >> Here you can find www_fdw feature documentation: >> http://wiki.postgresql.org/wiki/WWW_FDW > > Certainly looks useful (as a third-party extension, as others have already pointed out) +1. > What I didn't quite understand is how one would pass (dynamic) parameters for a GET request. For example, not too longago I needed to access the Google Maps API from postgres. I ended up using pl/python, and now wonder if your FDW wouldsupport that use-case. I'm working on a google_contacts_fdw to google contacts api [1] but stopped in the authentication design. As you can see in [2], for google api, you should get an authorization token and store the "Auth" value to use latter on the same "session". I'm wondering how the best way to "cache" this value as long as possible, because actually, when you need authentication for a FDW, you use the fdw_routine->BeginForeignScan call function but, in this situation, each SELECT to foreign table will do the handshake and some APIs could block this. Many client libraries work fine, caching the Auth value. How WWW_FDW could play with behaviors like that, since other Web APIs has the a authorization system like this [2]? [1] http://code.google.com/apis/contacts/docs/3.0/developers_guide.html [2] http://code.google.com/apis/gdata/articles/using_cURL.html Regards. -- Dickson S. Guedes mail/xmpp: guedes@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br
On Sep29, 2011, at 14:45 , Dickson S. Guedes wrote: > I'm working on a google_contacts_fdw to google contacts api [1] but > stopped in the authentication design. As you can see in [2], for > google api, you should get an authorization token and store the "Auth" > value to use latter on the same "session". I'm wondering how the best > way to "cache" this value as long as possible, because actually, when > you need authentication for a FDW, you use the > fdw_routine->BeginForeignScan call function but, in this situation, > each SELECT to foreign table will do the handshake and some APIs could > block this. Many client libraries work fine, caching the Auth value. > How WWW_FDW could play with behaviors like that, since other Web APIs > has the a authorization system like this [2]? You could use a hash table, allocated in the top-level memory context, to store one authentication token per combination of server and local user. I suggest you look at the MySQL FDW (https://github.com/dpage/mysql_fdw) - they presumably re-use the same connection over multiple foreign scans, which seems to be a problem similar to yours. best regards, Florian Pflug
2011/9/29 Florian Pflug <fgp@phlo.org>: > You could use a hash table, allocated in the top-level memory context, > to store one authentication token per combination of server and local user. In fact I started something in this way, with ldap_fdw, stashing the connection away using memory context and something using es_query_cxt from EState, just testing until now. How do this from PlanForeignScan I couldn't figure out yet. > I suggest you look at the MySQL FDW (https://github.com/dpage/mysql_fdw) > - they presumably re-use the same connection over multiple foreign scans, > which seems to be a problem similar to yours. From what I understand they re-use between BeginForeignScan and the subsequent IterateForeignScans and freeing at end. In my tests, there is a (re)connection for each SELECT * FROM ... I'm wondering that would be nice to have some built-in facilities (like this kind of "cache" between calls) provided by www_fdw, for that WWW API based FDWs. Regards. -- Dickson S. Guedes mail/xmpp: guedes@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br
On Sep29, 2011, at 16:43 , Dickson S. Guedes wrote: > 2011/9/29 Florian Pflug <fgp@phlo.org>: >> You could use a hash table, allocated in the top-level memory context, >> to store one authentication token per combination of server and local user. > > In fact I started something in this way, with ldap_fdw, stashing the > connection away using memory context and something using es_query_cxt > from EState, just testing until now. How do this from PlanForeignScan > I couldn't figure out yet. Maybe I'm missing something, but I'd say just allocate the hash table in TopMemoryContext (or however that's called) and store a reference to in a global variable. At least in the RESTful API case, you don't really need to worry about purging entries from the table I think. You might want to use <server, remote user> instead of <server, local user> as the key, though. That should avoid unnecessary authentication steps and hashtable entries if multiple local users are mapped to the same remote user, which is probably quite common for webservices. >> I suggest you look at the MySQL FDW (https://github.com/dpage/mysql_fdw) >> - they presumably re-use the same connection over multiple foreign scans, >> which seems to be a problem similar to yours. > > From what I understand they re-use between BeginForeignScan and the > subsequent IterateForeignScans and freeing at end. In my tests, there > is a (re)connection for each SELECT * FROM ... Oh, OK, I didn't know that. They're probably not the best model, then... best regards, Florian Pflug
Hello. I finished developing www_fdw: https://github.com/cyga/www_fdw/ It has docs/examples: https://github.com/cyga/www_fdw/wiki I haven't upload it to pgxn or pgfoundry yet. I want to ask for the following 2 things: 1. testing from community; 2. how can I add my extension to official fdw list: http://wiki.postgresql.org/wiki/Foreign_data_wrappers Is there any procedure for it? On Thu, Sep 29, 2011 at 1:20 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Florian Pflug <fgp@phlo.org> wrote: >> On Sep28, 2011, at 15:32 , Alexander Soudakov wrote: >>> Here you can find www_fdw feature documentation: >>> http://wiki.postgresql.org/wiki/WWW_FDW >> >> Certainly looks useful (as a third-party extension, as others have >> already pointed out) > > Our programmers agree that it is likely to be useful here. I agree > that it should be an extension. > >> What I didn't quite understand is how one would pass (dynamic) >> parameters for a GET request. For example, not too long ago I >> needed to access the Google Maps API from postgres. I ended up >> using pl/python, and now wonder if your FDW would support that >> use-case. > > I would assume that the usual ? to start parameters and & between > parameters would be used. For example, with Google Maps: > > http://maps.google.com/maps?hl=en&ie=UTF8&hq=&hnear=Madison,+Dane,+Wisconsin&ll=43.074684,-89.38188&spn=0.003006,0.00383&t=h&z=18&layer=c&cbll=43.07468,-89.381742&panoid=LhJ-PFHVzxRguJ6h616mmQ&cbp=12,355.53,,0,-1.32 > > -Kevin > -- Alexander Soudakov Software Developer email: cygakoB@gmail.com skype: asudakov
Also, in addition to: > I want to ask for the following 2 things: > 1. testing from community; > 2. how can I add my extension to official fdw list: > http://wiki.postgresql.org/wiki/Foreign_data_wrappers > Is there any procedure for it? Can I have it included in 9.2? On Sun, Nov 27, 2011 at 10:28 PM, Alexander Soudakov <cygakob@gmail.com> wrote: > Hello. > > I finished developing www_fdw: > https://github.com/cyga/www_fdw/ > > It has docs/examples: > https://github.com/cyga/www_fdw/wiki > > I haven't upload it to pgxn or pgfoundry yet. > > I want to ask for the following 2 things: > 1. testing from community; > 2. how can I add my extension to official fdw list: > http://wiki.postgresql.org/wiki/Foreign_data_wrappers > Is there any procedure for it? > > > > On Thu, Sep 29, 2011 at 1:20 AM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: >> Florian Pflug <fgp@phlo.org> wrote: >>> On Sep28, 2011, at 15:32 , Alexander Soudakov wrote: >>>> Here you can find www_fdw feature documentation: >>>> http://wiki.postgresql.org/wiki/WWW_FDW >>> >>> Certainly looks useful (as a third-party extension, as others have >>> already pointed out) >> >> Our programmers agree that it is likely to be useful here. I agree >> that it should be an extension. >> >>> What I didn't quite understand is how one would pass (dynamic) >>> parameters for a GET request. For example, not too long ago I >>> needed to access the Google Maps API from postgres. I ended up >>> using pl/python, and now wonder if your FDW would support that >>> use-case. >> >> I would assume that the usual ? to start parameters and & between >> parameters would be used. For example, with Google Maps: >> >> http://maps.google.com/maps?hl=en&ie=UTF8&hq=&hnear=Madison,+Dane,+Wisconsin&ll=43.074684,-89.38188&spn=0.003006,0.00383&t=h&z=18&layer=c&cbll=43.07468,-89.381742&panoid=LhJ-PFHVzxRguJ6h616mmQ&cbp=12,355.53,,0,-1.32 >> >> -Kevin >> > > > > -- > Alexander Soudakov > Software Developer > email: cygakoB@gmail.com > skype: asudakov > -- Alexander Soudakov Software Developer email: cygakoB@gmail.com skype: asudakov
Alexander Soudakov <cygakob@gmail.com> wrote: > in addition to: >> I want to ask for the following 2 things: >> 1. testing from community; >> 2. how can I add my extension to official fdw list: >> http://wiki.postgresql.org/wiki/Foreign_data_wrappers >> Is there any procedure for it? > > Can I have it included in 9.2? I think your best bet would be to review patch submission guidelines and submit it to the open CommitFest: http://wiki.postgresql.org/wiki/Submitting_a_Patch#Patch_submission https://commitfest.postgresql.org/action/commitfest_view/open -Kevin
On Sun, Nov 27, 2011 at 10:28 AM, Alexander Soudakov <cygakob@gmail.com> wrote: > Hello. > > I finished developing www_fdw: > https://github.com/cyga/www_fdw/ > > It has docs/examples: > https://github.com/cyga/www_fdw/wiki > > I haven't upload it to pgxn or pgfoundry yet. > > I want to ask for the following 2 things: > 1. testing from community; > 2. how can I add my extension to official fdw list: > http://wiki.postgresql.org/wiki/Foreign_data_wrappers > Is there any procedure for it? You need a community login to edit wiki. Go https://www.postgresql.org/account/login/?next=/account/ for sign up. Now that you have uploaded it to PGXN, I hope many people will test it. Regards, -- Hitoshi Harada