Re: 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(at)gmail(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)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(at)gmail(dot)com
google talk: cygakoB(at)gmail(dot)com
jabber: asudakov(at)jabber(dot)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(at)fetter(dot)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(at)dunslane(dot)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(at)dunslane(dot)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(at)sai(dot)msu(dot)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 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.

best regards,
Florian Pflug

Florian Pflug <fgp(at)phlo(dot)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(dot)Grittner(at)wicourts(dot)gov> wrote:
> Florian Pflug <fgp(at)phlo(dot)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(at)gmail(dot)com
skype: asudakov

2011/9/28 Florian Pflug <fgp(at)phlo(dot)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 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'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(at)guedesoft(dot)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(at)phlo(dot)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(at)guedesoft(dot)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(at)phlo(dot)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(dot)Grittner(at)wicourts(dot)gov> wrote:
> Florian Pflug <fgp(at)phlo(dot)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(at)gmail(dot)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(at)gmail(dot)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(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Florian Pflug <fgp(at)phlo(dot)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(at)gmail(dot)com
> skype: asudakov
>

--
Alexander Soudakov
Software Developer
email: cygakoB(at)gmail(dot)com
skype: asudakov

Alexander Soudakov <cygakob(at)gmail(dot)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(at)gmail(dot)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