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

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.

Demai

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

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

On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni <nidmgg(at)gmail(dot)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

Le mercredi 29 octobre 2014 12:49:12 Robert Haas a écrit :
> On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni <nidmgg(at)gmail(dot)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

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

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