Обсуждение: postgres FDW cost estimation options unrecognized in 9.3-beta1

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?

thanks

Lonni J Friedman <netllama@gmail.com> writes:
> 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?

[ experiments... ]  You need to say ADD, not SET, to add a new option to
the list.  SET might more appropriately be spelled REPLACE, because it
requires that the object already have a defined value for the option,
which will be replaced.

Our documentation appears not to disclose this fine point, but a look
at the SQL-MED standard says it's operating per spec.  The standard also
says that ADD is an error if the option is already defined, which is a
bit more defensible, but still not exactly what I'd call user-friendly.
And the error we issue for that case is pretty misleading too:

regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'true') ;
ALTER SERVER
regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'false') ;
ERROR:  option "use_remote_estimate" provided more than once

I think we could do with both more documentation, and better error
messages for these cases.  In the SET-where-you-should-use-ADD case,
perhaps

ERROR:  option "use_remote_estimate" has not been set
HINT: Use ADD not SET to define an option that wasn't already set.

In the ADD-where-you-should-use-SET case, perhaps

ERROR:  option "use_remote_estimate" is already set
HINT: Use SET not ADD to change an option's value.

The "provided more than once" wording would be appropriate if the same
option is specified more than once in the command text, but I'm not sure
that it's worth the trouble to detect that case.

Thoughts, better wordings?

            regards, tom lane

On Fri, Jul 26, 2013 at 3:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Lonni J Friedman <netllama@gmail.com> writes:
>> 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?
>
> [ experiments... ]  You need to say ADD, not SET, to add a new option to
> the list.  SET might more appropriately be spelled REPLACE, because it
> requires that the object already have a defined value for the option,
> which will be replaced.
>
> Our documentation appears not to disclose this fine point, but a look
> at the SQL-MED standard says it's operating per spec.  The standard also
> says that ADD is an error if the option is already defined, which is a
> bit more defensible, but still not exactly what I'd call user-friendly.
> And the error we issue for that case is pretty misleading too:
>
> regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'true') ;
> ALTER SERVER
> regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'false') ;
> ERROR:  option "use_remote_estimate" provided more than once
>
> I think we could do with both more documentation, and better error
> messages for these cases.  In the SET-where-you-should-use-ADD case,
> perhaps
>
> ERROR:  option "use_remote_estimate" has not been set
> HINT: Use ADD not SET to define an option that wasn't already set.
>
> In the ADD-where-you-should-use-SET case, perhaps
>
> ERROR:  option "use_remote_estimate" is already set
> HINT: Use SET not ADD to change an option's value.
>
> The "provided more than once" wording would be appropriate if the same
> option is specified more than once in the command text, but I'm not sure
> that it's worth the trouble to detect that case.
>
> Thoughts, better wordings?

Thanks Tom, I've confirmed that using ADD was the solution.  I think
your suggested updated ERROR & HINT text is an excellent improvement.
It definitely would have given me the clue I was missing earlier.

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

On Fri, Jul 26, 2013 at 06:28:05PM -0400, Tom Lane wrote:
> Our documentation appears not to disclose this fine point, but a look
> at the SQL-MED standard says it's operating per spec.  The standard also
> says that ADD is an error if the option is already defined, which is a
> bit more defensible, but still not exactly what I'd call user-friendly.
> And the error we issue for that case is pretty misleading too:
>
> regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'true') ;
> ALTER SERVER
> regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'false') ;
> ERROR:  option "use_remote_estimate" provided more than once
>
> I think we could do with both more documentation, and better error
> messages for these cases.  In the SET-where-you-should-use-ADD case,
> perhaps
>
> ERROR:  option "use_remote_estimate" has not been set
> HINT: Use ADD not SET to define an option that wasn't already set.
>
> In the ADD-where-you-should-use-SET case, perhaps
>
> ERROR:  option "use_remote_estimate" is already set
> HINT: Use SET not ADD to change an option's value.
>
> The "provided more than once" wording would be appropriate if the same
> option is specified more than once in the command text, but I'm not sure
> that it's worth the trouble to detect that case.

Where are on this?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

On 2/3/2014 4:59 AM, Rajni Baliyan wrote:
> Is there any way to automate the archive deletion process. Any script
> or command in HA setup using pgpool


please don't reply to an existing thread with a completely different
topic and subject matter.   your message is threaded under messages
about foreign data wrappers and cost estimation.

WAL archiving and pgpool have nothing to do with each other.

postgres manages the pg_xlog deletion process itself.   if you're
keeping an external WAL archive, then any such deletion depends entirely
on your requirements for the use of said archive.  if you want to be
able to do 'point in time recovery' aka PITR, you need ALL WAL archives
since the last base backup.  if the WAL archive is for streaming
replication to use to catch up after a service interruption, then
probably a day or two is all you need unless you envision longer
downtimes for the slave.

scripts for doing things like deleting old files are OS dependent. on a
(l)unix type system, its as simple as...

     find /path/to/files -mtime +2 | xargs rm

will delete everything over 2 days old in that path.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast