Hi,
PFA patch to fix the issue in foreign tables node where it was displaying
catalog tables in inherits options causing internal server error.
RM#1520
Additionally also moved hard coded sql for fetching types in template.
--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
index 98182fc..c171d01 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
@@ -349,8 +349,6 @@ class ForeignTableView(PGChildNodeView, DataTypeReader):
)
ver = self.manager.version
- server_type = self.manager.server_type
-
# Set template path for sql scripts depending
# on the server version.
@@ -495,15 +493,10 @@ shifted to the another schema.
foid: Foreign Table Id
"""
- condition = """typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
-AND NOT EXISTS (SELECT 1 FROM pg_class WHERE relnamespace=typnamespace
-AND relname = typname AND relkind != 'c') AND
-(typname NOT LIKE '_%' OR NOT EXISTS (SELECT 1 FROM pg_class WHERE
-relnamespace=typnamespace AND relname = substring(typname FROM 2)::name
-AND relkind != 'c'))"""
-
- if self.blueprint.show_system_objects:
- condition += " AND nsp.nspname != 'information_schema'"
+ condition = render_template("/".join(
+ [self.template_path, 'types_condition.sql']),
+ server_type=self.manager.server_type,
+ show_sys_objects=self.blueprint.show_system_objects)
# Get Types
status, types = self.get_types(self.conn, condition)
@@ -562,8 +555,10 @@ AND relkind != 'c'))"""
"""
res = []
try:
- SQL = render_template("/".join([self.template_path,
- 'get_tables.sql']), foid=foid)
+ SQL = render_template("/".join(
+ [self.template_path,'get_tables.sql']),
+ foid=foid, server_type=self.manager.server_type,
+ show_sys_objects=self.blueprint.show_system_objects)
status, rset = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/types_condition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/types_condition.sql
new file mode 100644
index 0000000..8e1d708
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/types_condition.sql
@@ -0,0 +1,14 @@
+{% import 'foreign_tables/sql/macros/db_catalogs.macro' as CATALOG %}
+typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
+AND NOT EXISTS (
+ SELECT 1 FROM pg_class
+ WHERE relnamespace=typnamespace
+ AND relname = typname AND relkind != 'c')
+ AND (typname NOT LIKE '_%' OR NOT EXISTS (
+ SELECT 1 FROM pg_class
+ WHERE relnamespace=typnamespace
+ AND relname = substring(typname FROM 2)::name
+ AND relkind != 'c'))
+{% if not show_system_objects %}
+ {{ CATALOG.VALID_TYPE_CATALOGS(server_type) }}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/types_condition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/types_condition.sql
new file mode 100644
index 0000000..8e1d708
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/types_condition.sql
@@ -0,0 +1,14 @@
+{% import 'foreign_tables/sql/macros/db_catalogs.macro' as CATALOG %}
+typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
+AND NOT EXISTS (
+ SELECT 1 FROM pg_class
+ WHERE relnamespace=typnamespace
+ AND relname = typname AND relkind != 'c')
+ AND (typname NOT LIKE '_%' OR NOT EXISTS (
+ SELECT 1 FROM pg_class
+ WHERE relnamespace=typnamespace
+ AND relname = substring(typname FROM 2)::name
+ AND relkind != 'c'))
+{% if not show_system_objects %}
+ {{ CATALOG.VALID_TYPE_CATALOGS(server_type) }}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql
index 38f9ce4..d35a531 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql
@@ -1,3 +1,4 @@
+{% import 'foreign_tables/sql/macros/db_catalogs.macro' as CATALOG %}
{% if attrelid %}
SELECT
array_agg(quote_ident(n.nspname) || '.' || quote_ident(c.relname)) as inherits
@@ -14,6 +15,9 @@ FROM
pg_class c, pg_namespace n
WHERE
c.relnamespace=n.oid AND c.relkind IN ('r', 'f')
+{% if not show_system_objects %}
+{{ CATALOG.VALID_CATALOGS(server_type) }}
+{% endif %}
{% if foid %}
AND c.oid <> {{foid}}::oid
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/types_condition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/types_condition.sql
new file mode 100644
index 0000000..8e1d708
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/types_condition.sql
@@ -0,0 +1,14 @@
+{% import 'foreign_tables/sql/macros/db_catalogs.macro' as CATALOG %}
+typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
+AND NOT EXISTS (
+ SELECT 1 FROM pg_class
+ WHERE relnamespace=typnamespace
+ AND relname = typname AND relkind != 'c')
+ AND (typname NOT LIKE '_%' OR NOT EXISTS (
+ SELECT 1 FROM pg_class
+ WHERE relnamespace=typnamespace
+ AND relname = substring(typname FROM 2)::name
+ AND relkind != 'c'))
+{% if not show_system_objects %}
+ {{ CATALOG.VALID_TYPE_CATALOGS(server_type) }}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/macros/db_catalogs.macro b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/macros/db_catalogs.macro
new file mode 100644
index 0000000..8a3866d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/macros/db_catalogs.macro
@@ -0,0 +1,11 @@
+{% macro VALID_CATALOGS(server_type) -%}
+ AND n.nspname NOT LIKE E'pg\_%' {% if server_type == 'ppas' %}
+AND n.nspname NOT IN ('information_schema', 'pgagent', 'dbo', 'sys') {% else %}
+AND n.nspname NOT IN ('information_schema') {% endif %}
+{%- endmacro %}
+{### Below macro is used in types fetching sql ###}
+{% macro VALID_TYPE_CATALOGS(server_type) -%}
+{% if server_type == 'ppas' %}
+ AND nsp.nspname NOT IN ('information_schema', 'pgagent', 'dbo', 'sys') {% else %}
+ AND nsp.nspname NOT IN ('information_schema') {% endif %}
+{%- endmacro %}
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
