CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw; ERROR: required option 'url' is missing CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url ''); ERROR: empty value in option 'url' CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'bar'); ERROR: invalid url: 'bar' CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://proxy.im'); /* * The statement above is the only one in this block that succeeds. Drop the * server again, otherwise every CREATE SERVER below fails with 'server "foo" * already exists' before the option validator is ever reached - which would * silently turn all of the option checks that follow into no-ops. */ DROP SERVER foo; CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout ''); ERROR: empty value in option 'connect_timeout' CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout '-1'); ERROR: invalid connect_timeout: '-1' DETAIL: Expected values are non-negative integers (timeout in seconds) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout 'abc'); ERROR: invalid connect_timeout: 'abc' DETAIL: Expected values are non-negative integers (timeout in seconds) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', request_timeout ''); ERROR: empty value in option 'request_timeout' CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', request_timeout '-1'); ERROR: invalid request_timeout: '-1' DETAIL: Expected values are non-negative integers (timeout in seconds, 0 = disabled) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', request_timeout 'abc'); ERROR: invalid request_timeout: 'abc' DETAIL: Expected values are non-negative integers (timeout in seconds, 0 = disabled) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', request_timeout '42 '); ERROR: invalid request_timeout: '42 ' DETAIL: Expected values are non-negative integers (timeout in seconds, 0 = disabled) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', max_response_size ''); ERROR: empty value in option 'max_response_size' CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', max_response_size '-1'); ERROR: invalid max_response_size: '-1' DETAIL: Expected values are non-negative integers (size in bytes, 0 = unlimited) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', max_response_size 'abc'); ERROR: invalid max_response_size: 'abc' DETAIL: Expected values are non-negative integers (size in bytes, 0 = unlimited) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', max_response_size '1.5'); ERROR: invalid max_response_size: '1.5' DETAIL: Expected values are non-negative integers (size in bytes, 0 = unlimited) CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout '42', max_connect_retry ''); ERROR: empty value in option 'max_connect_retry' CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout '42', max_connect_retry '-1'); ERROR: invalid max_connect_retry: '-1' DETAIL: Expected values are non-negative integers CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout '42', max_connect_retry '73', max_connect_redirect ''); ERROR: empty value in option 'max_connect_redirect' CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout '42', max_connect_retry '73', max_connect_redirect '-1'); ERROR: invalid max_connect_redirect: '-1' DETAIL: Expected values are non-negative integers /* valid timeout values - '0' disables the request timeout */ CREATE SERVER foo FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.im', connect_timeout '42', request_timeout '0'); SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'foo'; srvoptions ------------------------------------------------------------- {url=http://server.im,connect_timeout=42,request_timeout=0} (1 row) ALTER SERVER foo OPTIONS (SET request_timeout '30'); SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'foo'; srvoptions -------------------------------------------------------------- {url=http://server.im,connect_timeout=42,request_timeout=30} (1 row) /* invalid values must be rejected by ALTER SERVER as well */ ALTER SERVER foo OPTIONS (SET request_timeout '-1'); ERROR: invalid request_timeout: '-1' DETAIL: Expected values are non-negative integers (timeout in seconds, 0 = disabled) ALTER SERVER foo OPTIONS (SET request_timeout ''); ERROR: empty value in option 'request_timeout' ALTER SERVER foo OPTIONS (DROP request_timeout); SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'foo'; srvoptions ------------------------------------------- {url=http://server.im,connect_timeout=42} (1 row) DROP SERVER foo; /* invalid URL - retrying as set in 'max_connect_retry' */ CREATE SERVER srv FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.invalid', max_connect_retry '2', connect_timeout '15'); SELECT * FROM nominatim_search(server_name => 'srv', q => 'foo'); WARNING: request to 'http://server.invalid' failed (1/2) WARNING: request to 'http://server.invalid' failed (2/2) ERROR: Could not resolve host: server.invalid DETAIL: URL: "http://server.invalid/search?q=foo&format=xml&accept-language=en-US%2Cen%3Bq%3D0.9&bounded=0" HINT: Check your request parameters and credentials, if applicable, and try again. /* no retry! */ ALTER SERVER srv OPTIONS (SET max_connect_retry '0'); SELECT * FROM nominatim_search(server_name => 'srv', q => 'foo'); ERROR: Could not resolve host: server.invalid DETAIL: URL: "http://server.invalid/search?q=foo&format=xml&accept-language=en-US%2Cen%3Bq%3D0.9&bounded=0" HINT: Check your request parameters and credentials, if applicable, and try again. DROP SERVER srv; /* server does not exist */ SELECT * FROM nominatim_search(server_name => 'srv', q => 'bar'); ERROR: FOREIGN SERVER does not exist: 'srv' SELECT * FROM nominatim_search(server_name => 'srv', city => 'bar'); ERROR: FOREIGN SERVER does not exist: 'srv' SELECT * FROM nominatim_reverse(server_name => 'srv',lon => '1', lat => '2'); ERROR: FOREIGN SERVER does not exist: 'srv' SELECT * FROM nominatim_lookup(server_name => 'srv', osm_ids => 'W1'); ERROR: FOREIGN SERVER does not exist: 'srv' CREATE SERVER srv FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.invalid', connect_timeout '15', max_connect_retry '0'); /* bad request: 'q' and 'amenity' cannot be combined */ SELECT * FROM nominatim_search(server_name => 'srv', q => 'foo', amenity => 'bar'); ERROR: bad request => structured query parameters (amenity, street, city, county, state, postalcode, country) cannot be used together with 'q' parameter /* bad request: nothing to search for */ SELECT * FROM nominatim_search(server_name => 'srv'); ERROR: bad request => nothing to search for. HINT: A Nominatim Search request requires either a 'q' (free form parameter) or one of the structured query parameteres (amenity, street, city, county, state, postalcode, country) /* bad request: invalid layer */ SELECT * FROM nominatim_search(server_name => 'srv', q => 'foo', layer => 'bar'); WARNING: unrecognised layer 'bar' DETAIL: Known values are: address, poi, railway, natural, manmade ERROR: Could not resolve host: server.invalid DETAIL: URL: "http://server.invalid/search?q=foo&format=xml&accept-language=en-US%2Cen%3Bq%3D0.9&layer=bar&bounded=0" HINT: Check your request parameters and credentials, if applicable, and try again. /* FOREIGN TABLE not supported */ CREATE FOREIGN TABLE t (osm_id bigint OPTIONS (foo 'bar')) SERVER srv OPTIONS (foo 'bar'); ERROR: FOREIGN TABLE not supported DETAIL: The nominatim_fdw does not support FOREIGN TABLE mapping. Use the query functions instead. /* invalid user mapping options */ CREATE USER MAPPING FOR postgres SERVER srv OPTIONS (foo 'bar'); ERROR: invalid nominatim_fdw option 'foo' CREATE USER MAPPING FOR postgres SERVER srv OPTIONS (proxy_user 'u1', proxy_password ''); ERROR: empty value in option 'proxy_password' CREATE USER MAPPING FOR postgres SERVER srv OPTIONS (proxy_user '', proxy_password 'pw1'); ERROR: empty value in option 'proxy_user' CREATE USER MAPPING FOR postgres SERVER srv OPTIONS (proxy_user '', proxy_password ''); ERROR: empty value in option 'proxy_user' /* * nominatim_reverse() coordinate validation, including values every * comparison is false for (NaN). The helper reports the SQLSTATE and the * message up to the offending value, which older releases print as 'nan' * or 'inf' rather than 'NaN' or 'Infinity'. Accepted coordinates reach the * request, which fails on a host that never resolves (RFC 2606) - proof * that validation let them through. */ CREATE SERVER regress_coord_srv FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.invalid', max_connect_retry '0'); CREATE FUNCTION regress_reverse_check(p_lon double precision, p_lat double precision) RETURNS text LANGUAGE plpgsql AS $$ BEGIN PERFORM * FROM nominatim_reverse(server_name => 'regress_coord_srv', lon => p_lon, lat => p_lat); RETURN 'accepted'; EXCEPTION WHEN OTHERS THEN RETURN SQLSTATE || ': ' || split_part(SQLERRM, ':', 1); END $$; SELECT lon, lat, regress_reverse_check(lon, lat) AS result FROM (VALUES /* not a number */ (7.6::float8, 'NaN'::float8), ('NaN', 51.9), ('NaN', 'NaN'), /* infinite */ (7.6, 'Infinity'), (7.6, '-Infinity'), ('Infinity', 51.9), ('-Infinity', 51.9), /* just out of range */ (7.6, 90.0000001), (7.6, -90.0000001), (180.0000001, 51.9), (-180.0000001, 51.9), /* valid, including the boundaries */ (0, 0), (180, 90), (-180, -90) ) AS t(lon, lat); lon | lat | result --------------+-------------+------------------------------- 7.6 | NaN | 22003: latitude out of range NaN | 51.9 | 22003: longitude out of range NaN | NaN | 22003: latitude out of range 7.6 | Infinity | 22003: latitude out of range 7.6 | -Infinity | 22003: latitude out of range Infinity | 51.9 | 22003: longitude out of range -Infinity | 51.9 | 22003: longitude out of range 7.6 | 90.0000001 | 22003: latitude out of range 7.6 | -90.0000001 | 22003: latitude out of range 180.0000001 | 51.9 | 22003: longitude out of range -180.0000001 | 51.9 | 22003: longitude out of range 0 | 0 | HV00L: Could not resolve host 180 | 90 | HV00L: Could not resolve host -180 | -90 | HV00L: Could not resolve host (14 rows) DROP FUNCTION regress_reverse_check(double precision, double precision); DROP SERVER regress_coord_srv; /* * The 'url' option only accepts http and https, since requests are * restricted to those two protocols anyway. Terse verbosity: depending on * the libcurl build, an unsupported scheme is refused either by the scheme * check or already by the URL parser, with a different DETAIL. */ \set VERBOSITY terse /* one server name per case, so that a regression fails each case on its own */ CREATE SERVER regress_url_file FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'file:///etc/passwd'); ERROR: invalid url: 'file:///etc/passwd' CREATE SERVER regress_url_ftp FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'ftp://server.invalid'); ERROR: invalid url: 'ftp://server.invalid' CREATE SERVER regress_url_gopher FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'gopher://server.invalid'); ERROR: invalid url: 'gopher://server.invalid' CREATE SERVER regress_url_noscheme FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'server.invalid'); ERROR: invalid url: 'server.invalid' /* ALTER SERVER is checked the same way */ CREATE SERVER regress_url_alter FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.invalid'); ALTER SERVER regress_url_alter OPTIONS (SET url 'ftp://server.invalid'); ERROR: invalid url: 'ftp://server.invalid' SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'regress_url_alter'; srvoptions ----------------------------- {url=http://server.invalid} (1 row) DROP SERVER regress_url_alter; \set VERBOSITY default /* http and https are accepted, whatever the case of the scheme */ CREATE SERVER regress_url_http FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.invalid'); CREATE SERVER regress_url_https FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'https://server.invalid'); CREATE SERVER regress_url_upper FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'HTTPS://server.invalid'); CREATE SERVER regress_url_port_path FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'http://server.invalid:8080/nominatim/'); CREATE SERVER regress_url_query FOREIGN DATA WRAPPER nominatim_fdw OPTIONS (url 'https://server.invalid/?key=abc'); SELECT srvname, srvoptions FROM pg_foreign_server WHERE srvname LIKE 'regress\_url\_%' ORDER BY srvname; srvname | srvoptions -----------------------+--------------------------------------------- regress_url_http | {url=http://server.invalid} regress_url_https | {url=https://server.invalid} regress_url_port_path | {url=http://server.invalid:8080/nominatim/} regress_url_query | {url=https://server.invalid/?key=abc} regress_url_upper | {url=HTTPS://server.invalid} (5 rows) DROP SERVER regress_url_http; DROP SERVER regress_url_https; DROP SERVER regress_url_upper; DROP SERVER regress_url_port_path; DROP SERVER regress_url_query;