Re: [HACKERS] Foreign Data Wrapper

[Big Mike]

Inspecting the multicorn source was helpful.

For the purposes of anyone searching the mailing lists, I needed to access
the quals natively. The code that helped me figure out how to do this was
in the native odbc fdw here:
https://github.com/ZhengYang/odbc_fdw/blob/master/odbc_fdw.c

particularly the odbcBeginForeignScan() and odbcIterateForeignScan(), and
odbcGetQual() functions.


Thanks

On Fri, Nov 13, 2015 at 1:24 PM, Corey Huinker 
wrote:

> On Fri, Nov 13, 2015 at 1:46 PM, Big Mike  wrote:
>
>> Writing a Foreign Data Wrapper and interested in isolating the WHERE
>> clause to speed up the access of an indexed file on my filesystem. I'm
>> attempting to understand the inner workings of how the data is retrieved so
>> I'm writing code to just handle one case at the moment: WHERE clause on a
>> single column in the foreign 'table'.
>>
>> SELECT * FROM t WHERE testval = 1
>>
>> I have this code so far, an implementation of the IterateForeignScan
>> interface.
>>
>> static TupleTableSlot *
>> bIterateForeignScan(ForeignScanState *node) {
>> ...
>> RestrictInfo *rinfo = (RestrictInfo *)node->ss.ps.qual;
>> ...
>> }
>>
>> yet am not familiar with what I need to do to pick apart RestrictInfo in
>> order to gather 'testvar', '=', and '1' separately so I can interpret and
>> pass those through to my file parser.
>>
>> Am I going about this the correct way or is there another path I should
>> follow?
>>
>
>
> I would look at http://multicorn.org/ which gives you a working python
> framework. You subclass their ForeignDataWrapper class, override the
> __init__() and execute() functions, and that's about it. The execute()
> function has a list called quals that would set you up for the filtering
> you want to do.
>
> I would get the foreign data wrapper fully debugged this way before
> attempting to refactor in C. And when you do finally re-code, you can see
> what multicorn itself did to implement your filters.
>
>
>
>
>
>
>
>

Re: [HACKERS] Foreign Data Wrapper

[Corey Huinker]

On Fri, Nov 13, 2015 at 1:46 PM, Big Mike  wrote:

> Writing a Foreign Data Wrapper and interested in isolating the WHERE
> clause to speed up the access of an indexed file on my filesystem. I'm
> attempting to understand the inner workings of how the data is retrieved so
> I'm writing code to just handle one case at the moment: WHERE clause on a
> single column in the foreign 'table'.
>
> SELECT * FROM t WHERE testval = 1
>
> I have this code so far, an implementation of the IterateForeignScan
> interface.
>
> static TupleTableSlot *
> bIterateForeignScan(ForeignScanState *node) {
> ...
> RestrictInfo *rinfo = (RestrictInfo *)node->ss.ps.qual;
> ...
> }
>
> yet am not familiar with what I need to do to pick apart RestrictInfo in
> order to gather 'testvar', '=', and '1' separately so I can interpret and
> pass those through to my file parser.
>
> Am I going about this the correct way or is there another path I should
> follow?
>


I would look at http://multicorn.org/ which gives you a working python
framework. You subclass their ForeignDataWrapper class, override the
__init__() and execute() functions, and that's about it. The execute()
function has a list called quals that would set you up for the filtering
you want to do.

I would get the foreign data wrapper fully debugged this way before
attempting to refactor in C. And when you do finally re-code, you can see
what multicorn itself did to implement your filters.

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

[Ronan Dunklau]

Le mercredi 29 octobre 2014 14:16:23 Demai Ni a écrit :
 Robert and Ronan,
 
 many thanks for your response.
 
 I realized there is no clean way/api for it. maybe a hacking of ptree can
 do the trick.. :-)
 
 I will also take a look at IMPORT FOREIGN SCHEMA. However, for this
 requirement, I still need the user to input filename or filefolder, and I'd
 like to process the file(s) during create foreign table time, and save the
 processed result somewhere like ftoptions column in pg_foreign_table. may
 be some other way I can save the process result and make it assessable
 during query time?

You can pass options to IMPORT FOREIGN SCHEMA. So, you could maybe implement 
it so that the user can do that:

IMPORT FOREIGN SCHEMA public FROM SERVER file_server INTO local_schema OPTIONS  
(filename '/path/to/the/file');

Your FDW would then issue several CREATE FOREIGN TABLE statements, with all 
the necessary options.

Or even, to allow importing multiple files at once:

IMPORT FOREIGN SCHEMA public FROM SERVER file_server INTO local_schema OPTIONS  
(directory '/path/to/the/file_dir/');



 
 Demai
 
 On Wed, Oct 29, 2014 at 10:01 AM, Ronan Dunklau ronan.dunk...@dalibo.com
 
 wrote:
  Le mercredi 29 octobre 2014 12:49:12 Robert Haas a écrit :
   On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni nid...@gmail.com wrote:
I am looking for a couple pointers here about fdw, and how to change
  
  the
  
option values during CREATE table time.

I am using postgres-xc-1.2.1 right now. For example, it contains
  
  file_fdw,
  
whose create-table-stmt looks like:
CREATE FOREIGN TABLE t1()
SERVER file_server
OPTIONS(format 'text',filename 'testing.txt');

I would like to replace the 'testing.txt' with absolute path like
'/user/testor1/testing.txt', and make sure the new value is saved in
pg_foreign_table; the file_fdw_validator is used to validate the
  
  options,
  
but is there a way to replace the optionValue here? And get the new
  
  value
  
stored in pg_foreign_table?

Thanks

BTW, in my real use case, I am trying to interpret a hdfs file and
  
  would
  
need to save some hostname/port information in the option value, which
  
  not
  
necessary specified by user.
   
   I don't think there's going to be a clean way to do this.  The
   intention of the system is that the user-provided options are simply
   stored, not that the FDW author is going to use the options list to
   store their own bits and pieces.
  
  I would do that during the IMPORT FOREIGN SCHEMA statement. That way, the
  user
  doesn't have to specify those options: they would be generated at IMPORT
  time,
  and the user could change them later if really needed.
  
  --
  Ronan Dunklau
  http://dalibo.com - http://dalibo.org

-- 
Ronan Dunklau
http://dalibo.com - http://dalibo.org

signature.asc
Description: This is a digitally signed message part.

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

[Robert Haas]

On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni nid...@gmail.com wrote:
 I am looking for a couple pointers here about fdw, and how to change the
 option values during CREATE table time.

 I am using postgres-xc-1.2.1 right now. For example, it contains file_fdw,
 whose create-table-stmt looks like:
 CREATE FOREIGN TABLE t1()
 SERVER file_server
 OPTIONS(format 'text',filename 'testing.txt');

 I would like to replace the 'testing.txt' with absolute path like
 '/user/testor1/testing.txt', and make sure the new value is saved in
 pg_foreign_table; the file_fdw_validator is used to validate the options,
 but is there a way to replace the optionValue here? And get the new value
 stored in pg_foreign_table?

 Thanks

 BTW, in my real use case, I am trying to interpret a hdfs file and would
 need to save some hostname/port information in the option value, which not
 necessary specified by user.

I don't think there's going to be a clean way to do this.  The
intention of the system is that the user-provided options are simply
stored, not that the FDW author is going to use the options list to
store their own bits and pieces.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

[Ronan Dunklau]

Le mercredi 29 octobre 2014 12:49:12 Robert Haas a écrit :
 On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni nid...@gmail.com wrote:
  I am looking for a couple pointers here about fdw, and how to change the
  option values during CREATE table time.
  
  I am using postgres-xc-1.2.1 right now. For example, it contains file_fdw,
  whose create-table-stmt looks like:
  CREATE FOREIGN TABLE t1()
  SERVER file_server
  OPTIONS(format 'text',filename 'testing.txt');
  
  I would like to replace the 'testing.txt' with absolute path like
  '/user/testor1/testing.txt', and make sure the new value is saved in
  pg_foreign_table; the file_fdw_validator is used to validate the options,
  but is there a way to replace the optionValue here? And get the new value
  stored in pg_foreign_table?
  
  Thanks
  
  BTW, in my real use case, I am trying to interpret a hdfs file and would
  need to save some hostname/port information in the option value, which not
  necessary specified by user.
 
 I don't think there's going to be a clean way to do this.  The
 intention of the system is that the user-provided options are simply
 stored, not that the FDW author is going to use the options list to
 store their own bits and pieces.

I would do that during the IMPORT FOREIGN SCHEMA statement. That way, the user 
doesn't have to specify those options: they would be generated at IMPORT time, 
and the user could change them later if really needed.

-- 
Ronan Dunklau
http://dalibo.com - http://dalibo.org

signature.asc
Description: This is a digitally signed message part.

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

[Demai Ni]

Robert and Ronan,

many thanks for your response.

I realized there is no clean way/api for it. maybe a hacking of ptree can
do the trick.. :-)

I will also take a look at IMPORT FOREIGN SCHEMA. However, for this
requirement, I still need the user to input filename or filefolder, and I'd
like to process the file(s) during create foreign table time, and save the
processed result somewhere like ftoptions column in pg_foreign_table. may
be some other way I can save the process result and make it assessable
during query time?

Demai

On Wed, Oct 29, 2014 at 10:01 AM, Ronan Dunklau ronan.dunk...@dalibo.com
wrote:

 Le mercredi 29 octobre 2014 12:49:12 Robert Haas a écrit :
  On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni nid...@gmail.com wrote:
   I am looking for a couple pointers here about fdw, and how to change
 the
   option values during CREATE table time.
  
   I am using postgres-xc-1.2.1 right now. For example, it contains
 file_fdw,
   whose create-table-stmt looks like:
   CREATE FOREIGN TABLE t1()
   SERVER file_server
   OPTIONS(format 'text',filename 'testing.txt');
  
   I would like to replace the 'testing.txt' with absolute path like
   '/user/testor1/testing.txt', and make sure the new value is saved in
   pg_foreign_table; the file_fdw_validator is used to validate the
 options,
   but is there a way to replace the optionValue here? And get the new
 value
   stored in pg_foreign_table?
  
   Thanks
  
   BTW, in my real use case, I am trying to interpret a hdfs file and
 would
   need to save some hostname/port information in the option value, which
 not
   necessary specified by user.
 
  I don't think there's going to be a clean way to do this.  The
  intention of the system is that the user-provided options are simply
  stored, not that the FDW author is going to use the options list to
  store their own bits and pieces.

 I would do that during the IMPORT FOREIGN SCHEMA statement. That way, the
 user
 doesn't have to specify those options: they would be generated at IMPORT
 time,
 and the user could change them later if really needed.

 --
 Ronan Dunklau
 http://dalibo.com - http://dalibo.org

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

[Andrew Dunstan]

On 10/28/2014 05:26 PM, Demai Ni wrote:

hi, guys,

I am looking for a couple pointers here about fdw, and how to change 
the option values during CREATE table time.


I am using postgres-xc-1.2.1 right now. For example, it contains 
file_fdw, whose create-table-stmt looks like:

CREATE FOREIGN TABLE t1()
SERVER file_server
OPTIONS(format 'text',filename *'testing.txt'*);

I would like to replace the 'testing.txt' with absolute path like 
'/user/testor1/testing.txt', and make sure the new value is saved in 
pg_foreign_table; the file_fdw_validator is used to validate the 
options, but is there a way to replace the optionValue here? And get 
the new value stored in pg_foreign_table?


Thanks

BTW, in my real use case, I am trying to interpret a hdfs file and 
would need to save some hostname/port information in the option value, 
which not necessary specified by user.





This is the wrong list to ask this - it's a usage question that belongs 
on pgsql-general


See the documentation for ALTER FOREIGN TABLE.

cheers

andrew



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

Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?

[Demai Ni]

hi, Andrew,

thanks for the quick response.

M understanding is that Alter Foreign Table can change the option values by
user. What I need is to change the value programmatic inside foreign data
wrapper code, and hope someone already done so I can learn from the
existing design.

I thought this email list is for developer(ie, who change the code), and
pgsql-general for users. Hence, send to here. If it is wrong place, I will
switch the question over.

Anyway, appreciate the response. And good point on Alter-foreign-table,
probably some logic there I can copy over to create-table

Demai


On Tue, Oct 28, 2014 at 3:00 PM, Andrew Dunstan and...@dunslane.net wrote:


 On 10/28/2014 05:26 PM, Demai Ni wrote:

 hi, guys,

 I am looking for a couple pointers here about fdw, and how to change the
 option values during CREATE table time.

 I am using postgres-xc-1.2.1 right now. For example, it contains
 file_fdw, whose create-table-stmt looks like:
 CREATE FOREIGN TABLE t1()
 SERVER file_server
 OPTIONS(format 'text',filename *'testing.txt'*);

 I would like to replace the 'testing.txt' with absolute path like
 '/user/testor1/testing.txt', and make sure the new value is saved in
 pg_foreign_table; the file_fdw_validator is used to validate the options,
 but is there a way to replace the optionValue here? And get the new value
 stored in pg_foreign_table?

 Thanks

 BTW, in my real use case, I am trying to interpret a hdfs file and would
 need to save some hostname/port information in the option value, which not
 necessary specified by user.



 This is the wrong list to ask this - it's a usage question that belongs on
 pgsql-general

 See the documentation for ALTER FOREIGN TABLE.

 cheers

 andrew