alter server fails while setting multiple options at a time

[bala-cg]

Page No 957

• This alter fails when tried to run more than once trying to set 2 options (host and dbname) more than once, but works when ran with setting one option.

gpadmin=# create server myserver21 FOREIGN DATA WRAPPER dummy options (dbname 'test'); CREATE SERVER gpadmin=# gpadmin=# gpadmin=# ALTER SERVER myserver21 OPTIONS (set dbname 'test', host 'test'); ALTER SERVER gpadmin=# gpadmin=# ALTER SERVER myserver21 OPTIONS (set dbname 'test', host 'test'); ERROR: option "host" provided more than once gpadmin=# gpadmin=# ALTER SERVER myserver21 OPTIONS (set dbname 'test'); ALTER SERVER gpadmin=# gpadmin=# ALTER SERVER myserver21 OPTIONS (set host 'test'); ALTER SERVER gpadmin=#

[edespino]

@bala-cg I believe this is as designed. The ALTER SERVER default OPTIONS operation is ADD. You can see this in your output as well. After creating the server, the first ALTER works because the host value is ADDed. The set operation applies to the existing dbname. The next ALTER fails as the host option already has been added. If one wanted to ALTER both dbname and host values a command similar to the following would work: ALTER SERVER myserver21 OPTIONS (set dbname 'test2', set host 'test2');

For reference, here is the OPTIONS documentation and note the If no operation is explicitly specified, the default operation is ADD reference:

OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) Change the server's options. ADD, SET, and DROP specify the action to perform. If no operation is explicitly specified, the default operation is ADD. Option names must be unique. Greenplum Database validates names and values using the server's foreign-data wrapper library.

[edespino]

@bala-cg If ou agree, please close this issue.

[bala-cg]

Yes @edespino, this works as given in Documentation, I am closing it.

gpadmin=# create server myserver21 FOREIGN DATA WRAPPER dummy options (dbname 'test'); CREATE SERVER gpadmin=# gpadmin=# gpadmin=# gpadmin=# ALTER SERVER myserver21 OPTIONS (set dbname 'test', host 'test'); ALTER SERVER gpadmin=# gpadmin=# gpadmin=# ALTER SERVER myserver21 OPTIONS (set dbname 'test', set host 'test'); ALTER SERVER gpadmin=# ALTER SERVER myserver21 OPTIONS (set dbname 'test', set host 'test'); ALTER SERVER