Thread: Server postgres_fdw "fetch_size" option cannot be updated
Hi everyone, I'm playing around with postgres_fdw (version 1.0), and ran into an issue where I wanted to update the "fetch_size" optionon the server level: sql> CREATE SERVER test_remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgres2', dbname 'test', updatable 'false') [2017-12-28 10:30:13] completed in 35ms sql> ALTER SERVER test_remote OPTIONS (set fetch_size '50') [2017-12-28 10:30:26] [42704] ERROR: option "fetch_size" not found However, I am able to properly set the "fetch_size" when originally defining the server: sql> CREATE SERVER test_remote2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgres2', dbname 'test', updatable 'false',fetch_size '50') [2017-12-28 10:31:26] completed in 24ms Both the local and remote servers are running the same version of postgres: PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
On Thu, Dec 28, 2017 at 12:33 PM, Logan Owen <pgsql@s1devops.com> wrote:
Cheers,
Hi everyone,
I'm playing around with postgres_fdw (version 1.0), and ran into an issue where I wanted to update the "fetch_size" option on the server level:
sql> CREATE SERVER test_remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgres2', dbname 'test', updatable 'false')
[2017-12-28 10:30:13] completed in 35ms
sql> ALTER SERVER test_remote OPTIONS (set fetch_size '50')
[2017-12-28 10:30:26] [42704] ERROR: option "fetch_size" not found
However, I am able to properly set the "fetch_size" when originally defining the server:
sql> CREATE SERVER test_remote2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgres2', dbname 'test', updatable 'false', fetch_size '50')
[2017-12-28 10:31:26] completed in 24ms
You use 'set' only if a value already exists and you wish to change it. Since no value exists (the default does not count), you just do
ALTER SERVER test_remote OPTIONS (fetch_size '50');
Yes, this is confusing. But it is also documented, and I think it is required by the SQL/MED standard.
Jeff
On Thu, Dec 28, 2017, at 15:42, Jeff Janes wrote: > On Thu, Dec 28, 2017 at 12:33 PM, Logan Owen <pgsql@s1devops.com> wrote: >> Hi everyone, >> >> I'm playing around with postgres_fdw (version 1.0), and ran into an issue where I wanted to update the "fetch_size" optionon the server level: >> >> sql> CREATE SERVER test_remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgres2', dbname 'test', updatable'false') >> [2017-12-28 10:30:13] completed in 35ms >> sql> ALTER SERVER test_remote OPTIONS (set fetch_size '50') >> [2017-12-28 10:30:26] [42704] ERROR: option "fetch_size" not found >> >> However, I am able to properly set the "fetch_size" when originally defining the server: >> >> sql> CREATE SERVER test_remote2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgres2', dbname 'test', updatable'false', fetch_size '50') >> [2017-12-28 10:31:26] completed in 24ms >> > > You use 'set' only if a value already exists and you wish to change it. Since no value exists (the default does not count),you just do > > ALTER SERVER test_remote OPTIONS (fetch_size '50'); > > Yes, this is confusing. But it is also documented, and I think it is required by the SQL/MED standard. > > Cheers, > > Jeff Hi Jeff, Thank you for quick reply, and you are right, it is referenced in the documentation for ALTER SERVER (https://www.postgresql.org/docs/9.6/static/sql-alterserver.html): > Change options for the server. ADD, SET, and DROP specify the action to be performed. ADD is assumed if no operation isexplicitly specified. Option names must be unique; names and values are also validated using the server's foreign-datawrapper library. My apologies for not being more thorough before assuming an issue, and have a great day! Logan