pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedforeign data wrappers
37+ messages / 12 participants
[nested] [flat]
* foreign data wrappers
@ 2011-03-21 12:17 Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-21 12:17 UTC (permalink / raw)
To: pgsql-students@postgresql.org
Hi!
in the GSOC wiki, there is a potential project idea mentioning "Write Foreign Data Wrappers
for several external data sources (ODBC, SQL Server, Oracle, MySQL, CouchDB, Redis, etc.)"
Can anyone help explain how these wrapper are going to be like?
Thanks!
ZHENG Yang=
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-21 16:57 Selena Deckelmann <selena@chesnok.com>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Selena Deckelmann @ 2011-03-21 16:57 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: pgsql-students@postgresql.org
Hi!
On Mon, Mar 21, 2011 at 5:17 AM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> in the GSOC wiki, there is a potential project idea mentioning "Write
> Foreign Data Wrappers
> for several external data sources (ODBC, SQL Server, Oracle, MySQL,
> CouchDB, Redis, etc.)"
> Can anyone help explain how these wrapper are going to be like?
Have a look at http://wiki.postgresql.org/images/4/4c/SQLMED-FOSDEM2009.pdf
for starters. See if you can review that and come back with more
specific questions.
There are a few community members interested in seeing this move
forward, so this project is likely a great one for GSoC.
My suggestion would be to pick just one or two for this summer's project.
-selena
--
http://chesnok.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-21 18:45 Andrew Dunstan <andrew@dunslane.net>
parent: Selena Deckelmann <selena@chesnok.com>
0 siblings, 1 reply; 37+ messages in thread
From: Andrew Dunstan @ 2011-03-21 18:45 UTC (permalink / raw)
To: Selena Deckelmann <selena@chesnok.com>; +Cc: Zheng Yang <zhengyang4k@gmail.com>; pgsql-students@postgresql.org
On 03/21/2011 12:57 PM, Selena Deckelmann wrote:
> Hi!
>
> On Mon, Mar 21, 2011 at 5:17 AM, Zheng Yang<zhengyang4k@gmail.com> wrote:
>
>> in the GSOC wiki, there is a potential project idea mentioning "Write
>> Foreign Data Wrappers
>> for several external data sources (ODBC, SQL Server, Oracle, MySQL,
>> CouchDB, Redis, etc.)"
>> Can anyone help explain how these wrapper are going to be like?
> Have a look at http://wiki.postgresql.org/images/4/4c/SQLMED-FOSDEM2009.pdf
> for starters. See if you can review that and come back with more
> specific questions.
>
> There are a few community members interested in seeing this move
> forward, so this project is likely a great one for GSoC.
>
> My suggestion would be to pick just one or two for this summer's project.
>
Yes, but 9.1 *has* actual FDWs. The best thing to do (other than
attending my talk on Thursday :-) ) is to look at the file_fdw module in
git head, to see an actual working example.
cheers
andrew
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-21 19:49 Selena Deckelmann <selena@chesnok.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 2 replies; 37+ messages in thread
From: Selena Deckelmann @ 2011-03-21 19:49 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Zheng Yang <zhengyang4k@gmail.com>; pgsql-students@postgresql.org
On Mon, Mar 21, 2011 at 11:45 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> Yes, but 9.1 *has* actual FDWs. The best thing to do (other than attending
> my talk on Thursday :-) )
:) I'll be around, so hopefully I can attend your talk and post up some notes!
> is to look at the file_fdw module in git head, to
> see an actual working example.
I figured if I posted some wrong information someone would respond! :)
Zheng - here is a link to the existing code:
http://git.postgresql.org/gitweb?p=postgresql.git;a=tree;f=contrib/file_fdw
-selena
--
http://chesnok.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-22 10:51 Zheng Yang <zhengyang4k@gmail.com>
parent: Selena Deckelmann <selena@chesnok.com>
1 sibling, 0 replies; 37+ messages in thread
From: Zheng Yang @ 2011-03-22 10:51 UTC (permalink / raw)
To: Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; +Cc: pgsql-students@postgresql.org
Thanks! I will take a look!
ZY
On 22-Mar-2011, at 3:49 AM, Selena Deckelmann wrote:
> On Mon, Mar 21, 2011 at 11:45 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>> Yes, but 9.1 *has* actual FDWs. The best thing to do (other than attending
>> my talk on Thursday :-) )
>
> :) I'll be around, so hopefully I can attend your talk and post up some notes!
>
>> is to look at the file_fdw module in git head, to
>> see an actual working example.
>
> I figured if I posted some wrong information someone would respond! :)
>
> Zheng - here is a link to the existing code:
> http://git.postgresql.org/gitweb?p=postgresql.git;a=tree;f=contrib/file_fdw
>
> -selena
>
> --
> http://chesnok.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-24 05:39 Zheng Yang <zhengyang4k@gmail.com>
parent: Selena Deckelmann <selena@chesnok.com>
1 sibling, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-24 05:39 UTC (permalink / raw)
To: Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; +Cc: pgsql-students@postgresql.org
Hi guys,
I've figured out what a FDW does. It's quite clear from the doc that a FDW is an interface postgresql database used to communicate with
other data sources either it be a file, another DBMS or even weblinks (please correct me if i am wrong).
I have a few more specific questions on FDW. hope you guys can help!
1) by right the FDW should be implemented by the vendor of the datasource, is that correct?
2) what are the FDW that has been implemented? I've seen an plain file FWD module from the link provided by selena.
3) in one of the examples, when declaring an FDW, "..LIBRARY ’foosql_fdw.so’ LANGUAGE C;" the LANGUAGE option is going to be C only?
4) Is theFDW something similar to the Mysql storage engine architecture?
5) for the datalink, the doc has mentioned about it's going to be OS dependent. but i dun quite understand how does the kernel module and LD_PRELOAD will work in this case, esp.LD_PRELOAD.
That's the question so far. Thanks very much!
Regards,
Zheng Yang
On 22-Mar-2011, at 3:49 AM, Selena Deckelmann wrote:
> On Mon, Mar 21, 2011 at 11:45 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>> Yes, but 9.1 *has* actual FDWs. The best thing to do (other than attending
>> my talk on Thursday :-) )
>
> :) I'll be around, so hopefully I can attend your talk and post up some notes!
>
>> is to look at the file_fdw module in git head, to
>> see an actual working example.
>
> I figured if I posted some wrong information someone would respond! :)
>
> Zheng - here is a link to the existing code:
> http://git.postgresql.org/gitweb?p=postgresql.git;a=tree;f=contrib/file_fdw
>
> -selena
>
> --
> http://chesnok.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-24 07:54 Guillaume Lelarge <guillaume@lelarge.info>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Guillaume Lelarge @ 2011-03-24 07:54 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; pgsql-students@postgresql.org
Hi,
Le 24/03/2011 06:39, Zheng Yang a écrit :
> [...]
> I've figured out what a FDW does. It's quite clear from the doc that a FDW is an interface postgresql database used to communicate with
> other data sources either it be a file, another DBMS or even weblinks (please correct me if i am wrong).
>
You're right.
> I have a few more specific questions on FDW. hope you guys can help!
>
> 1) by right the FDW should be implemented by the vendor of the datasource, is that correct?
>
If you mean that an Oracle FDW should be implemented by Oracle, and an
Excel FDW should be implemented by Microsoft, then no, I don't think so.
AFAICS, anyone can write any FDW.
> 2) what are the FDW that has been implemented? I've seen an plain file FWD module from the link provided by selena.
>
Yeah, that's the one provided as a contrib module. I've also seen a
postgres one and a twitter one on pgsql-hackers. Andrew wrote two other
FDWs he'll talk about today at PGEast
(http://people.planetpostgresql.org/andrew/index.php?/archives/163-First-extension.html).
I hope he'll publish his slides somewhere really soon :)
> 3) in one of the examples, when declaring an FDW, "..LIBRARY ’foosql_fdw.so’ LANGUAGE C;" the LANGUAGE option is going to be C only?
>
According to
http://developer.postgresql.org/pgdocs/postgres/fdwhandler.html, yes.
> 4) Is theFDW something similar to the Mysql storage engine architecture?
>
Not really.
> 5) for the datalink, the doc has mentioned about it's going to be OS dependent. but i dun quite understand how does the kernel module and LD_PRELOAD will work in this case, esp.LD_PRELOAD.
>
No idea.
Writing some FDWs would make a really good GSoC project.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-24 08:49 Zheng Yang <zhengyang4k@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-24 08:49 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; pgsql-students@postgresql.org
Hi Guillaume,
Thanks very much! Your answers help a lot!
>
> Le 24/03/2011 06:39, Zheng Yang a écrit :
>
>> 4) Is theFDW something similar to the Mysql storage engine architecture?
>>
>
> Not really.
AFAIK,there is a CSV storage engine in MySql that serves as a wrapper, enable executing SQL queries directly on the file.
This makes me think that the CSV FDW was doing similar things.
>
>> 5) for the datalink, the doc has mentioned about it's going to be OS dependent. but i dun quite understand how does the kernel module and LD_PRELOAD will work in this case, esp.LD_PRELOAD.
>>
>
> No idea.
>
I am quite new to postgresql, but slowly picking up :) In general context, storing images directly inside DBMS fields as BLOBs was not considered as a good practice.
However, storing file directories or links may cause inconsistency. So I am quite interested in how this datalink concept can be implemented!
> Writing some FDWs would make a really good GSoC project.
Haha, agree! I believe that's also a great starting point for learning pgsql internals.
cheers
ZY
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-24 16:49 Alvaro Herrera <alvherre@commandprompt.com>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Alvaro Herrera @ 2011-03-24 16:49 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; pgsql-students <pgsql-students@postgresql.org>
Excerpts from Zheng Yang's message of jue mar 24 05:49:19 -0300 2011:
> I am quite new to postgresql, but slowly picking up :) In general context, storing images directly inside DBMS fields as BLOBs was not considered as a good practice.
> However, storing file directories or links may cause inconsistency. So I am quite interested in how this datalink concept can be implemented!
I'm not really sure how different would be handling the file linking in
an FDW. For example, if a transaction runs that deletes a file through
the FDW, and the transaction rolls back, how are you going to restore
the file to life?
(It sounds like you're trying to have a FDW that would present a
directory as a table, and each file in the dir as a row. Maybe it's not
a bad idea but it needs a lot more thought.)
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-24 16:55 Andrew Dunstan <andrew@dunslane.net>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 37+ messages in thread
From: Andrew Dunstan @ 2011-03-24 16:55 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Zheng Yang <zhengyang4k@gmail.com>; Guillaume Lelarge <guillaume@lelarge.info>; Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; pgsql-students <pgsql-students@postgresql.org>
On Thu, March 24, 2011 12:49 pm, Alvaro Herrera wrote:
> Excerpts from Zheng Yang's message of jue mar 24 05:49:19 -0300 2011:
>
>> I am quite new to postgresql, but slowly picking up :) In general
>> context, storing images directly inside DBMS fields as BLOBs was not
>> considered as a good practice.
>> However, storing file directories or links may cause inconsistency. So
>> I am quite interested in how this datalink concept can be implemented!
>
> I'm not really sure how different would be handling the file linking in
> an FDW. For example, if a transaction runs that deletes a file through
> the FDW, and the transaction rolls back, how are you going to restore
> the file to life?
>
> (It sounds like you're trying to have a FDW that would present a
> directory as a table, and each file in the dir as a row. Maybe it's not
> a bad idea but it needs a lot more thought.)
>
Currently FDWs can't store anything. They are read-only.
But I don't really buy this stuff about not storing images in the
database. I've done numbers of apps that do exactly that with great
success. If the images are huge that's another matter, but for small
images it works just fine.
cheers
andrew
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-24 20:59 Selena Deckelmann <selena@chesnok.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 37+ messages in thread
From: Selena Deckelmann @ 2011-03-24 20:59 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Zheng Yang <zhengyang4k@gmail.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
And, Andrew's slides from his talk today are now available:
http://people.planetpostgresql.org/andrew/uploads/fdw2.pdf
We really think the FDW projects would be great ones for GSoC. I know
we said that already. :)
-selena
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-25 00:53 Shiv <rama.theone@gmail.com>
parent: Selena Deckelmann <selena@chesnok.com>
0 siblings, 1 reply; 37+ messages in thread
From: Shiv @ 2011-03-25 00:53 UTC (permalink / raw)
To: Selena Deckelmann <selena@chesnok.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Zheng Yang <zhengyang4k@gmail.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Thank you for the slides. And yes FDW seems like a good project to get
involved in especially if one (and by one I mean me) is knew to the Postgres
codebase.
Regards,
Shiv
On Fri, Mar 25, 2011 at 4:59 AM, Selena Deckelmann <selena@chesnok.com>wrote:
> And, Andrew's slides from his talk today are now available:
> http://people.planetpostgresql.org/andrew/uploads/fdw2.pdf
>
> We really think the FDW projects would be great ones for GSoC. I know
> we said that already. :)
>
> -selena
>
> --
> Sent via pgsql-students mailing list (pgsql-students@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-students
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-25 02:07 Zheng Yang <zhengyang4k@gmail.com>
parent: Shiv <rama.theone@gmail.com>
0 siblings, 3 replies; 37+ messages in thread
From: Zheng Yang @ 2011-03-25 02:07 UTC (permalink / raw)
To: Shiv <rama.theone@gmail.com>; +Cc: Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Thanks for the slides! As what's mentioned on one of the slides, the datasource of FDW can be virtually everything.
So if there were a FDW for flickr API, the examples will be something like:
1) CREATE FOREIGN TABLE flickr_table (photo_id INT, ownerid INT, photo BLOB, text TEXT) SERVER flick_fdw OPTIONS (api_key 'AAAA');
p.s. the text field is used as the keyword for free text search.
when query from the table:
2) SELECT photo FROM flickr_table WHERE text = 'panda' LIMIT 0, 10;
This will return top ten photos relevant to 'panda'. Is my understanding correct?
I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan will always be performed irregardless of the sql statement,
the FDW is blind to the sql query performed, right?
And can anyone help explain what a planner is? What does this "provide cost estimates to planner" mean? Thanks!
Hi Shiv, I am also from National University of Singapore!
cheers,
ZY
On 25-Mar-2011, at 8:53 AM, Shiv wrote:
> Thank you for the slides. And yes FDW seems like a good project to get involved in especially if one (and by one I mean me) is knew to the Postgres codebase.
> Regards,
> Shiv
>
>
> On Fri, Mar 25, 2011 at 4:59 AM, Selena Deckelmann <selena@chesnok.com> wrote:
> And, Andrew's slides from his talk today are now available:
> http://people.planetpostgresql.org/andrew/uploads/fdw2.pdf
>
> We really think the FDW projects would be great ones for GSoC. I know
> we said that already. :)
>
> -selena
>
> --
> Sent via pgsql-students mailing list (pgsql-students@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-students
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-25 04:56 Shiv <rama.theone@gmail.com>
parent: Zheng Yang <zhengyang4k@gmail.com>
2 siblings, 0 replies; 37+ messages in thread
From: Shiv @ 2011-03-25 04:56 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Haha.. that's great! Geographical proximity is always nice!
The Flickr example is a good question. I would be interested to know if that
is allowable (under the specifications for FDW)
Regards,
Shiv
On Fri, Mar 25, 2011 at 10:07 AM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> Thanks for the slides! As what's mentioned on one of the slides, the
> datasource of FDW can be virtually everything.
>
> So if there were a FDW for flickr API, the examples will be something like:
>
> 1) CREATE FOREIGN TABLE flickr_table (photo_id INT, ownerid INT, photo
> BLOB, text TEXT) SERVER flick_fdw OPTIONS (api_key 'AAAA');
> p.s. the text field is used as the keyword for free text search.
>
> when query from the table:
> 2) SELECT photo FROM flickr_table WHERE text = 'panda' LIMIT 0, 10;
>
> This will return top ten photos relevant to 'panda'. Is my understanding
> correct?
>
>
> I've briefly gone through the slides. Regarding the 6 callbacks, is that
> correct to say that a full table scan will always be performed irregardless
> of the sql statement,
> the FDW is blind to the sql query performed, right?
>
> And can anyone help explain what a planner is? What does this "provide cost
> estimates to planner" mean? Thanks!
>
> Hi Shiv, I am also from National University of Singapore!
>
> cheers,
> ZY
>
>
> On 25-Mar-2011, at 8:53 AM, Shiv wrote:
>
> Thank you for the slides. And yes FDW seems like a good project to get
> involved in especially if one (and by one I mean me) is knew to the Postgres
> codebase.
> Regards,
> Shiv
>
>
> On Fri, Mar 25, 2011 at 4:59 AM, Selena Deckelmann <selena@chesnok.com>wrote:
>
>> And, Andrew's slides from his talk today are now available:
>> http://people.planetpostgresql.org/andrew/uploads/fdw2.pdf
>>
>> We really think the FDW projects would be great ones for GSoC. I know
>> we said that already. :)
>>
>> -selena
>>
>> --
>> Sent via pgsql-students mailing list (pgsql-students@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-students
>>
>
>
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-25 15:38 Selena Deckelmann <selena@chesnok.com>
parent: Zheng Yang <zhengyang4k@gmail.com>
2 siblings, 1 reply; 37+ messages in thread
From: Selena Deckelmann @ 2011-03-25 15:38 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Shiv <rama.theone@gmail.com>; Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Hi!
On Thu, Mar 24, 2011 at 7:07 PM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> Thanks for the slides! As what's mentioned on one of the slides, the
> datasource of FDW can be virtually everything.
> So if there were a FDW for flickr API, the examples will be something like:
> 1) CREATE FOREIGN TABLE flickr_table (photo_id INT, ownerid INT, photo BLOB,
> text TEXT) SERVER flick_fdw OPTIONS (api_key 'AAAA');
> p.s. the text field is used as the keyword for free text search.
> when query from the table:
Yes, but perhaps we could name the 'text' column something like
'search' or ... someone else could help come up with a better name. :)
> 2) SELECT photo FROM flickr_table WHERE text = 'panda' LIMIT 0, 10;
> This will return top ten photos relevant to 'panda'. Is my understanding
> correct?
Yes, the first ten results.
> I've briefly gone through the slides. Regarding the 6 callbacks, is that
> correct to say that a full table scan will always be performed irregardless
> of the sql statement,
> the FDW is blind to the sql query performed, right?
That's correct.
> And can anyone help explain what a planner is? What does this "provide cost
> estimates to planner" mean? Thanks!
Have a look at this for a quick overview:
http://www.postgresql.org/docs/9.0/static/planner-optimizer.html
-selena
--
http://chesnok.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-26 02:23 Zheng Yang <zhengyang4k@gmail.com>
parent: Selena Deckelmann <selena@chesnok.com>
0 siblings, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-26 02:23 UTC (permalink / raw)
To: Selena Deckelmann <selena@chesnok.com>; +Cc: Shiv <rama.theone@gmail.com>; Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Thanks!
Can anyone help on this:
I wanted to check out an copy of pgsql source so that I can start playing around with it.
I followed the working with git instructions here:
http://wiki.postgresql.org/wiki/Working_with_Git
but when I run this command:
git clone git://git.postgresql.org/git/postgresql.git
I get this error:
Cloning into gitpostgresql...
fatal: The remote end hung up unexpectedly
ZY
On 25-Mar-2011, at 11:38 PM, Selena Deckelmann wrote:
> Hi!
>
> On Thu, Mar 24, 2011 at 7:07 PM, Zheng Yang <zhengyang4k@gmail.com> wrote:
>> Thanks for the slides! As what's mentioned on one of the slides, the
>> datasource of FDW can be virtually everything.
>> So if there were a FDW for flickr API, the examples will be something like:
>> 1) CREATE FOREIGN TABLE flickr_table (photo_id INT, ownerid INT, photo BLOB,
>> text TEXT) SERVER flick_fdw OPTIONS (api_key 'AAAA');
>> p.s. the text field is used as the keyword for free text search.
>> when query from the table:
>
> Yes, but perhaps we could name the 'text' column something like
> 'search' or ... someone else could help come up with a better name. :)
>
>> 2) SELECT photo FROM flickr_table WHERE text = 'panda' LIMIT 0, 10;
>> This will return top ten photos relevant to 'panda'. Is my understanding
>> correct?
>
> Yes, the first ten results.
>
>> I've briefly gone through the slides. Regarding the 6 callbacks, is that
>> correct to say that a full table scan will always be performed irregardless
>> of the sql statement,
>> the FDW is blind to the sql query performed, right?
>
> That's correct.
>
>> And can anyone help explain what a planner is? What does this "provide cost
>> estimates to planner" mean? Thanks!
>
> Have a look at this for a quick overview:
> http://www.postgresql.org/docs/9.0/static/planner-optimizer.html
>
> -selena
>
> --
> http://chesnok.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-26 08:11 Dave Page <dpage@pgadmin.org>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Dave Page @ 2011-03-26 08:11 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Selena Deckelmann <selena@chesnok.com>; Shiv <rama.theone@gmail.com>; Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
On Sat, Mar 26, 2011 at 2:23 AM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> Thanks!
> Can anyone help on this:
> I wanted to check out an copy of pgsql source so that I can start playing
> around with it.
> I followed the working with git instructions here:
> http://wiki.postgresql.org/wiki/Working_with_Git
> but when I run this command:
>
> git clone git://git.postgresql.org/git/postgresql.git
>
> I get this error:
> Cloning into gitpostgresql...
> fatal: The remote end hung up unexpectedly
>
Try cloning over http:
git clone http://git.postgresql.org/git/postgresql.git
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-26 12:46 Zheng Yang <zhengyang4k@gmail.com>
parent: Dave Page <dpage@pgadmin.org>
0 siblings, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-26 12:46 UTC (permalink / raw)
To: Dave Page <dpage@pgadmin.org>; +Cc: Selena Deckelmann <selena@chesnok.com>; Shiv <rama.theone@gmail.com>; Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Hi,
On 26-Mar-2011, at 4:11 PM, Dave Page wrote:
>
> Try cloning over http:
>
> git clone http://git.postgresql.org/git/postgresql.git
>
It seems hanging there forever.
$git clone http://git.postgresql.org/git/postgresql.git
Cloning into postgresql...
ZY
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-26 12:48 Shiv <rama.theone@gmail.com>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: Shiv @ 2011-03-26 12:48 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Dave Page <dpage@pgadmin.org>; Selena Deckelmann <selena@chesnok.com>; Andrew Dunstan <andrew@dunslane.net>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Hey Zheng,
I don't seem to be having any issues. Something else might be going wrong.
Regards,
Shiv
On Sat, Mar 26, 2011 at 8:46 PM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> Hi,
>
> On 26-Mar-2011, at 4:11 PM, Dave Page wrote:
> >
> > Try cloning over http:
> >
> > git clone http://git.postgresql.org/git/postgresql.git
> >
>
> It seems hanging there forever.
>
> $git clone http://git.postgresql.org/git/postgresql.git
> Cloning into postgresql...
>
>
> ZY
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-26 15:07 Andrew Dunstan <andrew@dunslane.net>
parent: Zheng Yang <zhengyang4k@gmail.com>
2 siblings, 2 replies; 37+ messages in thread
From: Andrew Dunstan @ 2011-03-26 15:07 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
On 03/24/2011 10:07 PM, Zheng Yang wrote:
>
>
> I've briefly gone through the slides. Regarding the 6 callbacks, is
> that correct to say that a full table scan will always be performed
> irregardless of the sql statement,
> the FDW is blind to the sql query performed, right?
Yes, fairly much. If the feed is large you need some way to pass a limit
to the foreign side, possibly via table options. I'm fairly sure you
won't be able to get it via the SELECT statement.
>
> And can anyone help explain what a planner is? What does this "provide
> cost estimates to planner" mean? Thanks!
See <http://www.postgresql.org/docs/current/static/planner-optimizer.html;
If you're going to work on PostgreSQL code you need to read the docs.
cheers
andrew
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-27 12:51 Zheng Yang <zhengyang4k@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-27 12:51 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Hi,
I managed to clone a copy of 9.1devel and compiled an binary from it. I also did a make in the 'contrib/file_fdw/' folder and a file_fdw.so file is generated.
But the following error occurs when I was trying to experiment with the file FDW:
postgres=# CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
ERROR: could not access file "file_fdw": No such file or directory
STATEMENT: CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
ERROR: could not access file "file_fdw": No such file or directory
Did I miss anything?
ZY
On 26-Mar-2011, at 11:07 PM, Andrew Dunstan wrote:
>
>
> On 03/24/2011 10:07 PM, Zheng Yang wrote:
>>
>>
>> I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan will always be performed irregardless of the sql statement,
>> the FDW is blind to the sql query performed, right?
>
> Yes, fairly much. If the feed is large you need some way to pass a limit to the foreign side, possibly via table options. I'm fairly sure you won't be able to get it via the SELECT statement.
>
>>
>> And can anyone help explain what a planner is? What does this "provide cost estimates to planner" mean? Thanks!
>
>
> See <http://www.postgresql.org/docs/current/static/planner-optimizer.html;
>
> If you're going to work on PostgreSQL code you need to read the docs.
>
> cheers
>
> andrew
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-28 07:28 Guillaume Lelarge <guillaume@lelarge.info>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Guillaume Lelarge @ 2011-03-28 07:28 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
Le 27/03/2011 14:51, Zheng Yang a écrit :
> Hi,
>
> I managed to clone a copy of 9.1devel and compiled an binary from it. I also did a make in the 'contrib/file_fdw/' folder and a file_fdw.so file is generated.
>
> But the following error occurs when I was trying to experiment with the file FDW:
>
> postgres=# CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
> ERROR: could not access file "file_fdw": No such file or directory
> STATEMENT: CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
> ERROR: could not access file "file_fdw": No such file or directory
>
>
> Did I miss anything?
>
make install?
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-28 09:49 Zheng Yang <zhengyang4k@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 37+ messages in thread
From: Zheng Yang @ 2011-03-28 09:49 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
Haha.. Thanks, Guilaume. Forgot to install. It works now!
ZY
On 28-Mar-2011, at 3:28 PM, Guillaume Lelarge wrote:
> Le 27/03/2011 14:51, Zheng Yang a écrit :
>> Hi,
>>
>> I managed to clone a copy of 9.1devel and compiled an binary from it. I also did a make in the 'contrib/file_fdw/' folder and a file_fdw.so file is generated.
>>
>> But the following error occurs when I was trying to experiment with the file FDW:
>>
>> postgres=# CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
>> ERROR: could not access file "file_fdw": No such file or directory
>> STATEMENT: CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
>> ERROR: could not access file "file_fdw": No such file or directory
>>
>>
>> Did I miss anything?
>>
>
> make install?
>
>
> --
> Guillaume
> http://www.postgresql.fr
> http://dalibo.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-29 11:28 Zheng Yang <zhengyang4k@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-29 11:28 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-students <pgsql-students@postgresql.org>
Hi guys,
>>
>> I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan will always be performed irregardless of the sql statement,
>> the FDW is blind to the sql query performed, right?
>
> Yes, fairly much. If the feed is large you need some way to pass a limit to the foreign side, possibly via table options. I'm fairly sure you won't be able to get it via the SELECT statement.
>
Regarding the previous flickr example, I'm wondering how this 'free text search' function can be done if the FDW is blind to the SELECT statement.
For instance, the following query is to retrieve a photo relevant to 'panda':
SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
In this case, the FDW can only open a connection to flickr web service and return the next 'row' .
The problem is that there are a huge number of photos in flickr server and retrieving them sequentially is not realistic.
Any ideas on how this can be done?
ZY
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-29 15:48 Guillaume Lelarge <guillaume@lelarge.info>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Guillaume Lelarge @ 2011-03-29 15:48 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
Le 29/03/2011 13:28, Zheng Yang a écrit :
> Hi guys,
>
>>>
>>> I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan will always be performed irregardless of the sql statement,
>>> the FDW is blind to the sql query performed, right?
>>
>> Yes, fairly much. If the feed is large you need some way to pass a limit to the foreign side, possibly via table options. I'm fairly sure you won't be able to get it via the SELECT statement.
>>
>
>
> Regarding the previous flickr example, I'm wondering how this 'free text search' function can be done if the FDW is blind to the SELECT statement.
>
> For instance, the following query is to retrieve a photo relevant to 'panda':
>
> SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>
> In this case, the FDW can only open a connection to flickr web service and return the next 'row' .
> The problem is that there are a huge number of photos in flickr server and retrieving them sequentially is not realistic.
> Any ideas on how this can be done?
>
It probably means that flickr is not a good example of a nice fdw.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-29 16:32 Andrew Dunstan <andrew@dunslane.net>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 37+ messages in thread
From: Andrew Dunstan @ 2011-03-29 16:32 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Zheng Yang <zhengyang4k@gmail.com>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
On 03/29/2011 11:48 AM, Guillaume Lelarge wrote:
> Le 29/03/2011 13:28, Zheng Yang a écrit :
>> Hi guys,
>>
>>>> I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan will always be performed irregardless of the sql statement,
>>>> the FDW is blind to the sql query performed, right?
>>> Yes, fairly much. If the feed is large you need some way to pass a limit to the foreign side, possibly via table options. I'm fairly sure you won't be able to get it via the SELECT statement.
>>>
>>
>> Regarding the previous flickr example, I'm wondering how this 'free text search' function can be done if the FDW is blind to the SELECT statement.
>>
>> For instance, the following query is to retrieve a photo relevant to 'panda':
>>
>> SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>>
>> In this case, the FDW can only open a connection to flickr web service and return the next 'row' .
>> The problem is that there are a huge number of photos in flickr server and retrieving them sequentially is not realistic.
>> Any ideas on how this can be done?
>>
> It probably means that flickr is not a good example of a nice fdw.
Neither of you are being very creative. As I mentioned above, you need
to embed this sort of stuff in table options.
so you would have something like:
create foreign table panda_flickr (photo bytea, ...)
server flickr_server
options (searchterm 'panda', maxrows '50');
select photo from panda_flickr;
cheers
andrew
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-29 16:35 Guillaume Lelarge <guillaume@lelarge.info>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 37+ messages in thread
From: Guillaume Lelarge @ 2011-03-29 16:35 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Zheng Yang <zhengyang4k@gmail.com>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
Le 29/03/2011 18:32, Andrew Dunstan a écrit :
>
>
> On 03/29/2011 11:48 AM, Guillaume Lelarge wrote:
>> Le 29/03/2011 13:28, Zheng Yang a écrit :
>>> Hi guys,
>>>
>>>>> I've briefly gone through the slides. Regarding the 6 callbacks, is
>>>>> that correct to say that a full table scan will always be performed
>>>>> irregardless of the sql statement,
>>>>> the FDW is blind to the sql query performed, right?
>>>> Yes, fairly much. If the feed is large you need some way to pass a
>>>> limit to the foreign side, possibly via table options. I'm fairly
>>>> sure you won't be able to get it via the SELECT statement.
>>>>
>>>
>>> Regarding the previous flickr example, I'm wondering how this 'free
>>> text search' function can be done if the FDW is blind to the SELECT
>>> statement.
>>>
>>> For instance, the following query is to retrieve a photo relevant to
>>> 'panda':
>>>
>>> SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>>>
>>> In this case, the FDW can only open a connection to flickr web
>>> service and return the next 'row' .
>>> The problem is that there are a huge number of photos in flickr
>>> server and retrieving them sequentially is not realistic.
>>> Any ideas on how this can be done?
>>>
>> It probably means that flickr is not a good example of a nice fdw.
>
>
> Neither of you are being very creative. As I mentioned above, you need
> to embed this sort of stuff in table options.
>
> so you would have something like:
>
> create foreign table panda_flickr (photo bytea, ...)
> server flickr_server
> options (searchterm 'panda', maxrows '50');
> select photo from panda_flickr;
>
This would work but means you need to create a new foreign table to
search something else.
So, yeah, it works, but it's not convenient.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-29 16:51 Andrew Dunstan <andrew@dunslane.net>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 2 replies; 37+ messages in thread
From: Andrew Dunstan @ 2011-03-29 16:51 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Zheng Yang <zhengyang4k@gmail.com>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
On 03/29/2011 12:35 PM, Guillaume Lelarge wrote:
> Le 29/03/2011 18:32, Andrew Dunstan a écrit :
>>
>> On 03/29/2011 11:48 AM, Guillaume Lelarge wrote:
>>> Le 29/03/2011 13:28, Zheng Yang a écrit :
>>>> Hi guys,
>>>>
>>>>>> I've briefly gone through the slides. Regarding the 6 callbacks, is
>>>>>> that correct to say that a full table scan will always be performed
>>>>>> irregardless of the sql statement,
>>>>>> the FDW is blind to the sql query performed, right?
>>>>> Yes, fairly much. If the feed is large you need some way to pass a
>>>>> limit to the foreign side, possibly via table options. I'm fairly
>>>>> sure you won't be able to get it via the SELECT statement.
>>>>>
>>>> Regarding the previous flickr example, I'm wondering how this 'free
>>>> text search' function can be done if the FDW is blind to the SELECT
>>>> statement.
>>>>
>>>> For instance, the following query is to retrieve a photo relevant to
>>>> 'panda':
>>>>
>>>> SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>>>>
>>>> In this case, the FDW can only open a connection to flickr web
>>>> service and return the next 'row' .
>>>> The problem is that there are a huge number of photos in flickr
>>>> server and retrieving them sequentially is not realistic.
>>>> Any ideas on how this can be done?
>>>>
>>> It probably means that flickr is not a good example of a nice fdw.
>>
>> Neither of you are being very creative. As I mentioned above, you need
>> to embed this sort of stuff in table options.
>>
>> so you would have something like:
>>
>> create foreign table panda_flickr (photo bytea, ...)
>> server flickr_server
>> options (searchterm 'panda', maxrows '50');
>> select photo from panda_flickr;
>>
> This would work but means you need to create a new foreign table to
> search something else.
>
> So, yeah, it works, but it's not convenient.
The other possibility is that you can dig down into the ForiegnScanState
object. The FDW routines are passed a ForeignScanState object which
contains a ScanState object which in turn contains a PlanState object
which has a list of quals. You probably need to dig quite a bit further
but that's a start.
cheers
andrew
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-30 03:00 Zheng Yang <zhengyang4k@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 0 replies; 37+ messages in thread
From: Zheng Yang @ 2011-03-30 03:00 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
Hi Andrew and Guillaume,
>
>
> On 03/29/2011 12:35 PM, Guillaume Lelarge wrote:
>> Le 29/03/2011 18:32, Andrew Dunstan a écrit :
>>>
>>> On 03/29/2011 11:48 AM, Guillaume Lelarge wrote:
>>>> Le 29/03/2011 13:28, Zheng Yang a écrit :
>>>>> Hi guys,
>>>>>
>>>>>>> I've briefly gone through the slides. Regarding the 6 callbacks, is
>>>>>>> that correct to say that a full table scan will always be performed
>>>>>>> irregardless of the sql statement,
>>>>>>> the FDW is blind to the sql query performed, right?
>>>>>> Yes, fairly much. If the feed is large you need some way to pass a
>>>>>> limit to the foreign side, possibly via table options. I'm fairly
>>>>>> sure you won't be able to get it via the SELECT statement.
>>>>>>
>>>>> Regarding the previous flickr example, I'm wondering how this 'free
>>>>> text search' function can be done if the FDW is blind to the SELECT
>>>>> statement.
>>>>>
>>>>> For instance, the following query is to retrieve a photo relevant to
>>>>> 'panda':
>>>>>
>>>>> SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>>>>>
>>>>> In this case, the FDW can only open a connection to flickr web
>>>>> service and return the next 'row' .
>>>>> The problem is that there are a huge number of photos in flickr
>>>>> server and retrieving them sequentially is not realistic.
>>>>> Any ideas on how this can be done?
>>>>>
>>>> It probably means that flickr is not a good example of a nice fdw.
>>>
>>> Neither of you are being very creative. As I mentioned above, you need
>>> to embed this sort of stuff in table options.
>>>
>>> so you would have something like:
>>>
>>> create foreign table panda_flickr (photo bytea, ...)
>>> server flickr_server
>>> options (searchterm 'panda', maxrows '50');
>>> select photo from panda_flickr;
>>>
>> This would work but means you need to create a new foreign table to
>> search something else.
>>
>> So, yeah, it works, but it's not convenient.
>
> The other possibility is that you can dig down into the ForiegnScanState object. The FDW routines are passed a ForeignScanState object which contains a ScanState object which in turn contains a PlanState object which has a list of quals. You probably need to dig quite a bit further but that's a start.
I think this is a common issue for all FDWs that need to access remote resources over a network. For example, if there were a Mysql FDW, a full table scan implies the whole table will be transferred over.
it is not quite efficient for large tables.
If a table size is 1GB, iterating the whole table row by row means those 1GB of data needs to be transferred over. This may take hours even if for an sql statement as simple as
SELECT * from table where id = 1;
cheers,
ZY
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-31 08:55 Zheng Yang <zhengyang4k@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 37+ messages in thread
From: Zheng Yang @ 2011-03-31 08:55 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Shiv <rama.theone@gmail.com>; Selena Deckelmann <selena@chesnok.com>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-students <pgsql-students@postgresql.org>
Hi guys,
What does this 'quantifiable results' mean for proposals? Thanks!
Regards,
ZY
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: foreign data wrappers
@ 2011-03-31 12:05 Selena Deckelmann <selena@chesnok.com>
parent: Zheng Yang <zhengyang4k@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: Selena Deckelmann @ 2011-03-31 12:05 UTC (permalink / raw)
To: Zheng Yang <zhengyang4k@gmail.com>; +Cc: pgsql-students <pgsql-students@postgresql.org>
Hi!
On Thu, Mar 31, 2011 at 1:55 AM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> What does this 'quantifiable results' mean for proposals? Thanks!
It means what will the project deliver in terms of a feature, or more
than one feature, that can be evaluated. Like:
* Implement a FDW for Drizzle that is capable of read-only access to
tables with datatypes of int, float, varchar, char and timedate.
etc.
-selena
--
http://chesnok.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Foreign Data Wrappers
@ 2024-09-07 00:55 Gus Spier <gus.spier@gmail.com>
0 siblings, 4 replies; 37+ messages in thread
From: Gus Spier @ 2024-09-07 00:55 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
I find myself in new territory, Foreign Data Wrappers (FDW). I stumble
closer to success through the documentation, youtube videos, and various
google resources. But I've come across a concept that intrigues me very
deeply.
If I understand the concepts correctly, FDW not only makes other databases
available, FDW also offers access to .csv files, plain text, or just about
anything that can be bullied into some kind of query-able order. Has anyone
ever tried to connect to redis or elasticache? If so, how did it work out?
Regards,
Gus
--
Gus
gus.spier@gmail.com
540-454-3074
“Characteropathic individuals adopt ideologies created by doctrinaire,
often schizoidal people, recast them into an active propaganda form, and
disseminate it with their characteristic pathological egotism and paranoid
intolerance for any philosophies which may differ from their own.” (*Political
Ponerology*, Andrew Lobaczewski, 1984)
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Foreign Data Wrappers
@ 2024-09-07 01:29 Christophe Pettus <xof@thebuild.com>
parent: Gus Spier <gus.spier@gmail.com>
3 siblings, 0 replies; 37+ messages in thread
From: Christophe Pettus @ 2024-09-07 01:29 UTC (permalink / raw)
To: Gus Spier <gus.spier@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
> On Sep 6, 2024, at 17:55, Gus Spier <gus.spier@gmail.com> wrote:
> If I understand the concepts correctly, FDW not only makes other databases available, FDW also offers access to .csv files, plain text, or just about anything that can be bullied into some kind of query-able order.
There two parts to FDWs: The code in the PostgreSQL core, and the FDW extension that is specific to type of the remote data source. It's up to the plug-in to translate the remote data source into a format that appears as a foreign table, so the specifics of how the remote data source are mapped to the table depend on the plug-ins implementation. You need to install the FDW extension in the PostgreSQL instance as you do with any extension. There are a lot of different plug-ins, of varying degrees to maintenance, out in the ecosystem. They definitely exist for Redis; I'm not sure about Elasticache.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Foreign Data Wrappers
@ 2024-09-07 01:32 Ian Lawrence Barwick <barwick@gmail.com>
parent: Gus Spier <gus.spier@gmail.com>
3 siblings, 0 replies; 37+ messages in thread
From: Ian Lawrence Barwick @ 2024-09-07 01:32 UTC (permalink / raw)
To: Gus Spier <gus.spier@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
2024年9月7日(土) 9:55 Gus Spier <gus.spier@gmail.com>:
>
> I find myself in new territory, Foreign Data Wrappers (FDW). I stumble closer to success through the documentation, youtube videos, and various google resources. But I've come across a concept that intrigues me very deeply.
>
> If I understand the concepts correctly, FDW not only makes other databases available, FDW also offers access to .csv files, plain text, or just about anything that can be bullied into some kind of query-able order. Has anyone ever tried to connect to redis or elasticache? If so, how did it work out?
There are two FDW implementations listed for Redis here:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers#NoSQL_Database_Wrappers
but neither have been updated for recent PostgreSQL versions.
Regards
Ian Barwick
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Foreign Data Wrappers
@ 2024-09-07 01:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gus Spier <gus.spier@gmail.com>
3 siblings, 1 reply; 37+ messages in thread
From: Tom Lane @ 2024-09-07 01:35 UTC (permalink / raw)
To: Gus Spier <gus.spier@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Gus Spier <gus.spier@gmail.com> writes:
> If I understand the concepts correctly, FDW not only makes other databases
> available, FDW also offers access to .csv files, plain text, or just about
> anything that can be bullied into some kind of query-able order. Has anyone
> ever tried to connect to redis or elasticache? If so, how did it work out?
Looks like it's been done:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
(No warranty expressed or implied on the quality of these
particular FDWs.)
regards, tom lane
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Foreign Data Wrappers
@ 2024-09-07 01:41 Gus Spier <gus.spier@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 37+ messages in thread
From: Gus Spier @ 2024-09-07 01:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Thanks for the pointers!
I'll resume my task in the morning. If I get anywhere, I will post the
outcome.
R,
Gus
On Fri, Sep 6, 2024 at 9:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Gus Spier <gus.spier@gmail.com> writes:
> > If I understand the concepts correctly, FDW not only makes other
> databases
> > available, FDW also offers access to .csv files, plain text, or just
> about
> > anything that can be bullied into some kind of query-able order. Has
> anyone
> > ever tried to connect to redis or elasticache? If so, how did it work
> out?
>
> Looks like it's been done:
>
> https://wiki.postgresql.org/wiki/Foreign_data_wrappers
>
> (No warranty expressed or implied on the quality of these
> particular FDWs.)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Foreign Data Wrappers
@ 2024-09-07 02:40 Brent Wood <pcreso@yahoo.com>
parent: Gus Spier <gus.spier@gmail.com>
3 siblings, 0 replies; 37+ messages in thread
From: Brent Wood @ 2024-09-07 02:40 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>; Gus Spier <gus.spier@gmail.com>
The most interesting (IMHO) FDW implementation I have used is an Australian WFS service mounted locally as a Postgis table.
Not wonderful performance, but full spatial query functionality.
If performance was an issue, "create table as select * from FDW;" creates a local cache...
Very useful functionality.
Brent Wood
On Saturday, September 7, 2024 at 12:55:37 PM GMT+12, Gus Spier <gus.spier@gmail.com> wrote:
I find myself in new territory, Foreign Data Wrappers (FDW). I stumble closer to success through the documentation, youtube videos, and various google resources. But I've come across a concept that intrigues me very deeply.
If I understand the concepts correctly, FDW not only makes other databases available, FDW also offers access to .csv files, plain text, or just about anything that can be bullied into some kind of query-able order. Has anyone ever tried to connect to redis or elasticache? If so, how did it work out?
Regards,Gus
--
Gus
gus.spier@gmail.com540-454-3074
“Characteropathic individuals adopt ideologies created by doctrinaire, often schizoidal people, recast them into an active propaganda form, and disseminate it with their characteristic pathological egotism and paranoid intolerance for any philosophies which may differ from their own.” (Political Ponerology, Andrew Lobaczewski, 1984)
^ permalink raw reply [nested|flat] 37+ messages in thread
end of thread, other threads:[~2024-09-07 02:40 UTC | newest]
Thread overview: 37+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-21 12:17 foreign data wrappers Zheng Yang <zhengyang4k@gmail.com>
2011-03-21 16:57 ` Selena Deckelmann <selena@chesnok.com>
2011-03-21 18:45 ` Andrew Dunstan <andrew@dunslane.net>
2011-03-21 19:49 ` Selena Deckelmann <selena@chesnok.com>
2011-03-22 10:51 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-24 05:39 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-24 07:54 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-24 08:49 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-24 16:49 ` Alvaro Herrera <alvherre@commandprompt.com>
2011-03-24 16:55 ` Andrew Dunstan <andrew@dunslane.net>
2011-03-24 20:59 ` Selena Deckelmann <selena@chesnok.com>
2011-03-25 00:53 ` Shiv <rama.theone@gmail.com>
2011-03-25 02:07 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-25 04:56 ` Shiv <rama.theone@gmail.com>
2011-03-25 15:38 ` Selena Deckelmann <selena@chesnok.com>
2011-03-26 02:23 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-26 08:11 ` Dave Page <dpage@pgadmin.org>
2011-03-26 12:46 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-26 12:48 ` Shiv <rama.theone@gmail.com>
2011-03-26 15:07 ` Andrew Dunstan <andrew@dunslane.net>
2011-03-27 12:51 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-28 07:28 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-28 09:49 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-29 11:28 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-29 15:48 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-29 16:32 ` Andrew Dunstan <andrew@dunslane.net>
2011-03-29 16:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-29 16:51 ` Andrew Dunstan <andrew@dunslane.net>
2011-03-30 03:00 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-31 08:55 ` Zheng Yang <zhengyang4k@gmail.com>
2011-03-31 12:05 ` Selena Deckelmann <selena@chesnok.com>
2024-09-07 00:55 Foreign Data Wrappers Gus Spier <gus.spier@gmail.com>
2024-09-07 01:29 ` Re: Foreign Data Wrappers Christophe Pettus <xof@thebuild.com>
2024-09-07 01:32 ` Re: Foreign Data Wrappers Ian Lawrence Barwick <barwick@gmail.com>
2024-09-07 01:35 ` Re: Foreign Data Wrappers Tom Lane <tgl@sss.pgh.pa.us>
2024-09-07 01:41 ` Re: Foreign Data Wrappers Gus Spier <gus.spier@gmail.com>
2024-09-07 02:40 ` Re: Foreign Data Wrappers Brent Wood <pcreso@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox