Re: postgres FDW cost estimation options unrecognized in 9.3-beta1

Hi Lonni,

> Greetings,
> I have a postgresql-9.3-beta1 cluster setup (from the
> yum.postgresql.org RPMs), where I'm experimenting with the postgres
> FDW extension. The documentation (
> http://www.postgresql.org/docs/9.3/static/postgres-fdw.html )
> references three Cost Estimation Options which can be set for a
> foreign table or a foreign server. However when I attempt to set
> them, I always get an error that the option is not found:
> #######
> nightly=# show SERVER_VERSION ;
> server_version
> ----------------
> 9.3beta1
>
> nightly=# \des+
> List of
> foreign servers
> Name | Owner | Foreign-data wrapper | Access privileges |
> Type | Version |
> FDW Options | Description
> -----------+-----------+----------------------+-------------------+------+---------+----------
> -----------------------------------------+-------------
> cuda_db10 | lfriedman | postgres_fdw | |
> | | (host 'cuda-db10', dbname 'nightly', port '5432') |
> (1 row)
>
> nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ;
> ERROR: option "use_remote_estimate" not found
> #######
>
> Am I doing something wrong, or is this a bug?

You got this error because you can't alter, in a server, an option which
is not yet defined using 'SET'.
You could do in this way if your server was already created with the
option 'use_remote_estimate' set, just for instance, to 'false':

nightly=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------+-----------+----------------------+-------------------+------+---------+-------------------------------------------------------------------------------+-------------
cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'false') |
(1 row)

nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ;
ALTER SERVER

nightly=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------+-----------+----------------------+-------------------+------+---------+-------------------------------------------------------------------------------+-------------
cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true') |
(1 row)

If your server was not created with any 'use_remote_estimate' option,
you have to add it in this way:

nightly=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------+-----------+----------------------+-------------------+------+---------+-------------------- ------------------------------+-------------
cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432') |
(1 row)

nightly=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'true') ;
ALTER SERVER

nightly=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------+-----------+----------------------+-------------------+------+---------+-------------------------------------------------------------------------------+-------------
cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true') |
(1 row)

To create your server with 'use_remote_estimate' option already set to
'true' you have to do:

nightly=# CREATE SERVER cuda_db10 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true');
CREATE SERVER

nightly=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------+-----------+----------------------+-------------------+------+---------+-------------------------------------------------------------------------------+-------------
cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true') |
(1 row)

Hope this can help.
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it