public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][patch]: Fix for RM 1320 3+ messages / 2 participants [nested] [flat]
* [pgAdmin4][patch]: Fix for RM 1320 @ 2016-07-25 05:32 Neel Patel <[email protected]> 2016-07-25 06:01 ` Re: [pgAdmin4][patch]: Fix for RM 1320 Neel Patel <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Neel Patel @ 2016-07-25 05:32 UTC (permalink / raw) To: pgadmin-hackers Hi, Please find attached patch file for the fix of RM- 1320. RM-1320 - Unable to create foreign data wrapper when user change the schema of extension 'postgres_fdw_handler'. Thanks, Neel Patel -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] RM_1320.patch (13.8K, 3-RM_1320.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/__init__.py index d8481eb..8efd50f 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/__init__.py @@ -689,7 +689,7 @@ class ForeignDataWrapperView(PGChildNodeView): return internal_server_error(errormsg=r_set) for row in r_set['rows']: - res.append({'label': row['fdwvalue'], 'value': row['fdwvalue']}) + res.append({'label': row['schema_prefix_fdw_val'], 'value': row['schema_prefix_fdw_val']}) return make_json_response(data=res, status=200) @@ -715,7 +715,7 @@ class ForeignDataWrapperView(PGChildNodeView): return internal_server_error(errormsg=r_set) for row in r_set['rows']: - res.append({'label': row['fdwhan'], 'value': row['fdwhan']}) + res.append({'label': row['schema_prefix_fdw_hand'], 'value': row['schema_prefix_fdw_hand']}) return make_json_response( data=res, diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/create.sql index 996fd71..17241df 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/create.sql @@ -3,9 +3,9 @@ {% if data.name %} CREATE FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }}{% if data.fdwvalue %} - VALIDATOR {{ conn|qtIdent(data.fdwvalue) }}{%endif%}{% if data.fdwhan %} + VALIDATOR {{ data.fdwvalue }}{%endif%}{% if data.fdwhan %} - HANDLER {{ conn|qtIdent(data.fdwhan) }}{% endif %}{% if data.fdwoptions %} + HANDLER {{ data.fdwhan }}{% endif %}{% if data.fdwoptions %} {% set addAlter = "False" %} {% for variable in data.fdwoptions %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/handlers.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/handlers.sql index 50d27c6..e4f4185 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/handlers.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/handlers.sql @@ -1,2 +1,5 @@ {# ============= Get the handlers of foreign data wrapper ============= #} -SELECT proname as fdwhan FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace WHERE pronargs=0 AND prorettype=3115; \ No newline at end of file +SELECT nspname, proname as fdwhan, + quote_ident(nspname)||'.'||quote_ident(proname) AS schema_prefix_fdw_hand +FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace +WHERE pronargs=0 AND prorettype=3115; diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/properties.sql index af28d13..b982549 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/properties.sql @@ -1,9 +1,13 @@ {# ============= Get all the properties of foreign data wrapper ============= #} -SELECT fdw.oid as fdwoid, fdwname as name, fdwhandler, fdwvalidator, vh.proname as fdwhan, vp.proname as fdwvalue, description, - array_to_string(fdwoptions, ',') AS fdwoptions, pg_get_userbyid(fdwowner) as fdwowner, array_to_string(fdwacl::text[], ', ') as acl +SELECT fdw.oid as fdwoid, fdwname as name, fdwhandler, fdwvalidator, description, + array_to_string(fdwoptions, ',') AS fdwoptions, pg_get_userbyid(fdwowner) as fdwowner, array_to_string(fdwacl::text[], ', ') as acl, + quote_ident(vp_nsp.nspname)||'.'||quote_ident(vp.proname) AS fdwvalue, + quote_ident(vh_nsp.nspname)||'.'||quote_ident(vh.proname) AS fdwhan FROM pg_foreign_data_wrapper fdw LEFT OUTER JOIN pg_proc vh on vh.oid=fdwhandler LEFT OUTER JOIN pg_proc vp on vp.oid=fdwvalidator +LEFT OUTER JOIN pg_namespace vh_nsp ON vh_nsp.oid=vh.pronamespace +LEFT OUTER JOIN pg_namespace vp_nsp ON vp_nsp.oid=vp.pronamespace LEFT OUTER JOIN pg_description des ON (des.objoid=fdw.oid AND des.objsubid=0 AND des.classoid='pg_foreign_data_wrapper'::regclass) {% if fid %} WHERE fdw.oid={{fid}}::int @@ -11,4 +15,4 @@ WHERE fdw.oid={{fid}}::int {% if fname %} WHERE fdw.fdwname={{ fname|qtLiteral }}::text {% endif %} -ORDER BY fdwname \ No newline at end of file +ORDER BY fdwname diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/update.sql index 3061c20..451ea74 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/update.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/update.sql @@ -15,7 +15,7 @@ ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} {# ============= Update foreign data wrapper validator ============= #} {% if data.fdwvalue and data.fdwvalue != o_data.fdwvalue %} ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} - VALIDATOR {{ conn|qtIdent(data.fdwvalue) }}; + VALIDATOR {{ data.fdwvalue }}; {% endif %} {% if data.fdwvalue == '' and data.fdwvalue != o_data.fdwvalue %} @@ -26,7 +26,7 @@ ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} {# ============= Update foreign data wrapper handler ============= #} {% if data.fdwhan and data.fdwhan != o_data.fdwhan %} ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} - HANDLER {{ conn|qtIdent(data.fdwhan) }}; + HANDLER {{ data.fdwhan }}; {% endif %} {% if data.fdwhan == '' and data.fdwhan != o_data.fdwhan %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/validators.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/validators.sql index 6f3d019..f12cc48 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/validators.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.1_plus/validators.sql @@ -1,2 +1,5 @@ {# ============= Get the validators of foreign data wrapper ============= #} -SELECT proname as fdwvalue FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace WHERE proargtypes[0]=1009 AND proargtypes[1]=26; +SELECT nspname, proname as fdwvalue, + quote_ident(nspname)||'.'||quote_ident(proname) AS schema_prefix_fdw_val +FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace +WHERE proargtypes[0]=1009 AND proargtypes[1]=26; diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/create.sql index 3d81c67..3d33a8e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/create.sql @@ -3,9 +3,9 @@ {% if data.name %} CREATE FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }}{% if data.fdwvalue %} - VALIDATOR {{ conn|qtIdent(data.fdwvalue) }}{%endif%}{% if data.fdwhan %} + VALIDATOR {{ data.fdwvalue }}{%endif%}{% if data.fdwhan %} - HANDLER {{ conn|qtIdent(data.fdwhan) }}{% endif %}{% if data.fdwoptions %} + HANDLER {{ data.fdwhan }}{% endif %}{% if data.fdwoptions %} {% set addAlter = "False" %} {% for variable in data.fdwoptions %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/handlers.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/handlers.sql index 50d27c6..e4f4185 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/handlers.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/handlers.sql @@ -1,2 +1,5 @@ {# ============= Get the handlers of foreign data wrapper ============= #} -SELECT proname as fdwhan FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace WHERE pronargs=0 AND prorettype=3115; \ No newline at end of file +SELECT nspname, proname as fdwhan, + quote_ident(nspname)||'.'||quote_ident(proname) AS schema_prefix_fdw_hand +FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace +WHERE pronargs=0 AND prorettype=3115; diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/properties.sql index daede2a..a07c335 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/properties.sql @@ -1,9 +1,13 @@ {# ============= Get all the properties of foreign data wrapper ============= #} -SELECT fdw.oid as fdwoid, fdwname as name, fdwhandler, fdwvalidator, vh.proname as fdwhan, vp.proname as fdwvalue, description, - array_to_string(fdwoptions, ',') AS fdwoptions, pg_get_userbyid(fdwowner) as fdwowner, array_to_string(fdwacl::text[], ', ') as acl +SELECT fdw.oid as fdwoid, fdwname as name, fdwhandler, fdwvalidator, description, + array_to_string(fdwoptions, ',') AS fdwoptions, pg_get_userbyid(fdwowner) as fdwowner, array_to_string(fdwacl::text[], ', ') as acl, + quote_ident(vp_nsp.nspname)||'.'||quote_ident(vp.proname) AS fdwvalue, + quote_ident(vh_nsp.nspname)||'.'||quote_ident(vh.proname) AS fdwhan FROM pg_foreign_data_wrapper fdw LEFT OUTER JOIN pg_proc vh on vh.oid=fdwhandler LEFT OUTER JOIN pg_proc vp on vp.oid=fdwvalidator + LEFT OUTER JOIN pg_namespace vh_nsp ON vh_nsp.oid=vh.pronamespace + LEFT OUTER JOIN pg_namespace vp_nsp ON vp_nsp.oid=vp.pronamespace LEFT OUTER JOIN pg_description des ON (des.objoid=fdw.oid AND des.objsubid=0 AND des.classoid='pg_foreign_data_wrapper'::regclass) {% if fid %} WHERE fdw.oid={{fid}}::int diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/update.sql index 7a01632..41860cd 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/update.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/update.sql @@ -15,7 +15,7 @@ ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} {# ============= Update foreign data wrapper validator ============= #} {% if data.fdwvalue and data.fdwvalue != o_data.fdwvalue %} ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} - VALIDATOR {{ conn|qtIdent(data.fdwvalue) }}; + VALIDATOR {{ data.fdwvalue }}; {% endif %} {% if data.fdwvalue == '' and data.fdwvalue != o_data.fdwvalue %} @@ -26,7 +26,7 @@ ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} {# ============= Update foreign data wrapper handler ============= #} {% if data.fdwhan and data.fdwhan != o_data.fdwhan %} ALTER FOREIGN DATA WRAPPER {{ conn|qtIdent(data.name) }} - HANDLER {{ conn|qtIdent(data.fdwhan) }}; + HANDLER {{ data.fdwhan }}; {% endif %} {% if data.fdwhan == '' and data.fdwhan != o_data.fdwhan %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/validators.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/validators.sql index 6f3d019..f12cc48 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/validators.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/9.3_plus/validators.sql @@ -1,2 +1,5 @@ {# ============= Get the validators of foreign data wrapper ============= #} -SELECT proname as fdwvalue FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace WHERE proargtypes[0]=1009 AND proargtypes[1]=26; +SELECT nspname, proname as fdwvalue, + quote_ident(nspname)||'.'||quote_ident(proname) AS schema_prefix_fdw_val +FROM pg_proc p JOIN pg_namespace nsp ON nsp.oid=pronamespace +WHERE proargtypes[0]=1009 AND proargtypes[1]=26; ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgAdmin4][patch]: Fix for RM 1320 2016-07-25 05:32 [pgAdmin4][patch]: Fix for RM 1320 Neel Patel <[email protected]> @ 2016-07-25 06:01 ` Neel Patel <[email protected]> 2016-07-25 11:48 ` Re: [pgAdmin4][patch]: Fix for RM 1320 Dave Page <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Neel Patel @ 2016-07-25 06:01 UTC (permalink / raw) To: pgadmin-hackers Adding more details. *Root cause:-* When user change schema of already created fdw extension then while creating the new fdw we should append the <schema_name>.<fdw_handler_name>. Previously we added only <fdw_handler_name> so while executing the query it will not be able to find fdw handler name without schema name. e.g. If user changed the fdw handler 'postgres_fdw_handler' under schema 'test' then we should display the handler name under fdw as "test. postgres_fdw_handler". *Solution:-* With this patch, we have added <schema_name>.<fdw_handler_name> so while creating or updating the fdw, proper sql will be generated. Thanks, Neel Patel On Mon, Jul 25, 2016 at 11:02 AM, Neel Patel <[email protected]> wrote: > Hi, > > Please find attached patch file for the fix of RM- 1320. > > RM-1320 - Unable to create foreign data wrapper when user change the > schema of extension 'postgres_fdw_handler'. > > Thanks, > Neel Patel > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgAdmin4][patch]: Fix for RM 1320 2016-07-25 05:32 [pgAdmin4][patch]: Fix for RM 1320 Neel Patel <[email protected]> 2016-07-25 06:01 ` Re: [pgAdmin4][patch]: Fix for RM 1320 Neel Patel <[email protected]> @ 2016-07-25 11:48 ` Dave Page <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Dave Page @ 2016-07-25 11:48 UTC (permalink / raw) To: Neel Patel <[email protected]>; +Cc: pgadmin-hackers Thanks - applied. On Mon, Jul 25, 2016 at 7:01 AM, Neel Patel <[email protected]> wrote: > Adding more details. > > Root cause:- > > When user change schema of already created fdw extension then while creating > the new fdw we should append the <schema_name>.<fdw_handler_name>. > Previously we added only <fdw_handler_name> so while executing the query it > will not be able to find fdw handler name without schema name. > > e.g. If user changed the fdw handler 'postgres_fdw_handler' under schema > 'test' then we should display the handler name under fdw as "test. > postgres_fdw_handler". > > Solution:- > > With this patch, we have added <schema_name>.<fdw_handler_name> so while > creating or updating the fdw, proper sql will be generated. > > Thanks, > Neel Patel > > > On Mon, Jul 25, 2016 at 11:02 AM, Neel Patel <[email protected]> > wrote: >> >> Hi, >> >> Please find attached patch file for the fix of RM- 1320. >> >> RM-1320 - Unable to create foreign data wrapper when user change the >> schema of extension 'postgres_fdw_handler'. >> >> Thanks, >> Neel Patel > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-07-25 11:48 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-07-25 05:32 [pgAdmin4][patch]: Fix for RM 1320 Neel Patel <[email protected]> 2016-07-25 06:01 ` Neel Patel <[email protected]> 2016-07-25 11:48 ` Dave Page <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox