Re: foreign data wrappers

Hi,

I am wondering if someone can help me understand the following query
execution behavior:

I have two similar tables and table2 consists of 5000000 records.

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: A ms.

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: B ms.

Sometimes B >> A (B is very larger than A). Both queries are same and run
twice. What could be the reason that the same query is taking very long
sometime?

Thanks a lot!

Hi!

This mailing list is for GSoC students to discuss their proposals.

Please direct your question to pgsql-general(at)postgresql(dot)org(dot)

-selena

On Sun, Mar 20, 2011 at 9:59 PM, preetika tyagi <preetikatyagi(at)gmail(dot)com> wrote:
> Hi,
> I am wondering if someone can help me understand the following query
> execution behavior:
> I have two similar tables and table2 consists of 5000000 records.
> Query: INSERT INTO table1 SELECT * FROM table2.
> Execution Time: A ms.
> Query: INSERT INTO table1 SELECT * FROM table2.
> Execution Time: B ms.
> Sometimes B >> A (B is very larger than A). Both queries are same and run
> twice. What could be the reason that the same query is taking very long
> sometime?
> Thanks a lot!
>

--
http://chesnok.com

Hi,

I am wondering if someone can help me understand the following query
execution behavior:

I have two similar tables and table2 consists of 5000000 records.

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: A ms.

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: B ms.

Sometimes B >> A (B is very larger than A). Both queries are same and run
twice. What could be the reason that the same query is taking very long
sometime?

Thanks a lot!

On Mar 21, 2011, at 10:42 AM, preetika tyagi wrote:

> Query: INSERT INTO table1 SELECT * FROM table2.
> Execution Time: A ms.
>
> Query: INSERT INTO table1 SELECT * FROM table2.
> Execution Time: B ms

If session is same, then
B < A if the data is cached due to execution of first statement.
B > A, if the there is any change happened Or Any session has evicted the cached data.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

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

Thank you, Vibhor!

I am wondering if caching will make such a big difference. For example, if A
is 15 minutes, then B is 1.5 hrs.
What could be other factors in case database server is separated from
clients (on different computer systems)?

Preetika

On Mon, Mar 21, 2011 at 2:48 AM, Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com
> wrote:

>
> On Mar 21, 2011, at 10:42 AM, preetika tyagi wrote:
>
> > Query: INSERT INTO table1 SELECT * FROM table2.
> > Execution Time: A ms.
> >
> > Query: INSERT INTO table1 SELECT * FROM table2.
> > Execution Time: B ms
>
> If session is same, then
> B < A if the data is cached due to execution of first statement.
> B > A, if the there is any change happened Or Any session has evicted the
> cached data.
>
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor(dot)kumar(at)enterprisedb(dot)com
> Blog:http://vibhork.blogspot.com
>
>

On Mar 21, 2011, at 9:55 AM, preetika tyagi wrote:

> For example, if A is 15 minutes, then B is 1.5 hrs.

Well, considering that random disk access is on the order of 10,000 times slower than RAM...

But you can answer the question yourself by comparing the query run against cold caches (after a reboot, or various command-line tricks to purge cache) vs against warm caches (twice back-to-back).

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice

Hi!

On Mon, Mar 21, 2011 at 5:17 AM, Zheng Yang <zhengyang4k(at)gmail(dot)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

Thank you, Scott!
I tried running the same query after reboot and back-to-back, it was taking
less time in both the cases. It means the problem is something else.

Can there be a reason which is more hardware/operating system specific and
due to which the behavior is not uniform?

Preetika

On Mon, Mar 21, 2011 at 9:06 AM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>wrote:

> On Mar 21, 2011, at 9:55 AM, preetika tyagi wrote:
>
> > For example, if A is 15 minutes, then B is 1.5 hrs.
>
> Well, considering that random disk access is on the order of 10,000 times
> slower than RAM...
>
> But you can answer the question yourself by comparing the query run against
> cold caches (after a reboot, or various command-line tricks to purge cache)
> vs against warm caches (twice back-to-back).
>
> --
> Scott Ribe
> scott_ribe(at)elevated-dev(dot)com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>

On 03/21/2011 12:57 PM, Selena Deckelmann wrote:
> Hi!
>
> On Mon, Mar 21, 2011 at 5:17 AM, Zheng Yang<zhengyang4k(at)gmail(dot)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

On Mon, Mar 21, 2011 at 11:45 AM, Andrew Dunstan <andrew(at)dunslane(dot)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

On Mar 21, 2011, at 12:03 PM, preetika tyagi wrote:

> I tried running the same query after reboot and back-to-back, it was taking less time in both the cases. It means the problem is something else.
>
> Can there be a reason which is more hardware/operating system specific and due to which the behavior is not uniform?

While I do have a couple of ideas, you're probably better served by letting those here with more optimization experience help you, as their answers will be more complete.

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice

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(at)dunslane(dot)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

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(at)dunslane(dot)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

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

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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

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(at)chesnok(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-students
>

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(at)chesnok(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-students
>

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(at)gmail(dot)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(at)chesnok(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-students
>>
>
>
>

Hi!

On Thu, Mar 24, 2011 at 7:07 PM, Zheng Yang <zhengyang4k(at)gmail(dot)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

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

On Sat, Mar 26, 2011 at 2:23 AM, Zheng Yang <zhengyang4k(at)gmail(dot)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

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

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

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

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
>

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

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

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

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

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

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

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

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

Hi guys,

What does this 'quantifiable results' mean for proposals? Thanks!

Regards,
ZY

Hi!

On Thu, Mar 31, 2011 at 1:55 AM, Zheng Yang <zhengyang4k(at)gmail(dot)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