Re: postgres FDW cost estimation options unrecognized in 9.3-beta1 - Mailing list pgsql-general

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.broccolo@2ndQuadrant.it | www.2ndQuadrant.it