Hello Hackers,
In the attached patches we enable the display of partitions on GreenPlum
database and also correct SQL that was being used in the SQL tab form
Schemas
Thanks
Joao
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/acl.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/acl.sql
index e1e64be6..a4a53863 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/acl.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/acl.sql
@@ -13,9 +13,24 @@ FROM
ELSE 'UNKNOWN - ' || (d).privilege_type
END AS privilege_type
FROM
- (SELECT aclexplode(nsp.nspacl) as d
- FROM pg_namespace nsp
- WHERE nsp.oid = {{ scid|qtLiteral }}::OID
+ (
+ SELECT
+ u_grantor.oid AS grantor,
+ grantee.oid AS grantee,
+ pr.type AS privilege_type,
+ aclcontains(nc.nspacl, makeaclitem(grantee.oid, u_grantor.oid,
pr.type, true)) AS is_grantable
+ FROM pg_namespace nc, pg_authid u_grantor, (
+ SELECT pg_authid.oid, pg_authid.rolname
+ FROM pg_authid
+ UNION ALL
+ SELECT 0::oid AS oid, 'PUBLIC') grantee(oid, rolname),
+ ( SELECT 'CREATE'
+ UNION ALL
+ SELECT 'USAGE') pr(type)
+ WHERE aclcontains(nc.nspacl, makeaclitem(grantee.oid, u_grantor.oid,
pr.type, false))
+ AND (pg_has_role(u_grantor.oid, 'USAGE'::text) OR
pg_has_role(grantee.oid, 'USAGE'::text)
+ OR grantee.rolname = 'PUBLIC'::name)
+ AND nsp.oid = {{ scid|qtLiteral }}::OID
) a
) b
LEFT JOIN pg_catalog.pg_roles g ON (b.grantor = g.oid)
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/defacl.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/defacl.sql
index 91c3085d..191e40cc 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/defacl.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/defacl.sql
@@ -1,40 +1,7 @@
SELECT
- CASE (a.deftype)
- WHEN 'r' THEN 'deftblacl'
- WHEN 'S' THEN 'defseqacl'
- WHEN 'f' THEN 'deffuncacl'
- WHEN 'T' THEN 'deftypeacl'
- ELSE 'UNKNOWN - ' || a.deftype
- END AS deftype,
- COALESCE(gt.rolname, 'PUBLIC') grantee, g.rolname grantor,
array_agg(a.privilege_type) as privileges, array_agg(a.is_grantable) as
grantable
-FROM
- (SELECT
- (acl).grantee as grantee, (acl).grantor AS grantor, (acl).is_grantable
AS is_grantable,
- CASE (acl).privilege_type
- WHEN 'CONNECT' THEN 'c'
- WHEN 'CREATE' THEN 'C'
- WHEN 'DELETE' THEN 'd'
- WHEN 'EXECUTE' THEN 'X'
- WHEN 'INSERT' THEN 'a'
- WHEN 'REFERENCES' THEN 'x'
- WHEN 'SELECT' THEN 'r'
- WHEN 'TEMPORARY' THEN 'T'
- WHEN 'TRIGGER' THEN 't'
- WHEN 'TRUNCATE' THEN 'D'
- WHEN 'UPDATE' THEN 'w'
- WHEN 'USAGE' THEN 'U'
- ELSE 'UNKNOWN - ' || (acl).privilege_type
- END AS privilege_type,
- defaclobjtype as deftype
- FROM
- (SELECT defaclobjtype, aclexplode(defaclacl) as acl
- FROM
- pg_namespace nsp
- LEFT OUTER JOIN pg_catalog.pg_default_acl dacl ON
(dacl.defaclnamespace = nsp.oid)
- WHERE
- nsp.oid={{scid}}::oid
- ) d) a
- LEFT JOIN pg_catalog.pg_roles g ON (a.grantor = g.oid)
- LEFT JOIN pg_catalog.pg_roles gt ON (a.grantee = gt.oid)
-GROUP BY g.rolname, gt.rolname, a.deftype
-ORDER BY a.deftype;
+ '' AS deftype,
+ '' AS grantee,
+ '' AS grantor,
+ '' AS grantor,
+ '' AS privileges,
+ '' AS grantable
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/properties.sql
index ca2254d0..cce103ca 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/properties.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/gpdb_5.0_plus/sql/properties.sql
@@ -13,30 +13,13 @@ SELECT
WHEN nspname LIKE E'pg\\_%' THEN true
ELSE false END AS is_sys_object,
{### Default ACL for Tables ###}
- (SELECT array_to_string(ARRAY(
- SELECT array_to_string(defaclacl::text[], ', ')
- FROM pg_default_acl
- WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid
- ), ', ')) AS tblacl,
+ '' AS tblacl,
{### Default ACL for Sequnces ###}
- (SELECT array_to_string(ARRAY(
- SELECT array_to_string(defaclacl::text[], ', ')
- FROM pg_default_acl
- WHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid
- ), ', ')) AS seqacl,
+ '' AS seqacl,
{### Default ACL for Functions ###}
- (SELECT array_to_string(ARRAY(
- SELECT array_to_string(defaclacl::text[], ', ')
- FROM pg_default_acl
- WHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid
- ), ', ')) AS funcacl,
+ '' AS funcacl,
{### Default ACL for Type ###}
- (SELECT array_to_string(ARRAY(
- SELECT array_to_string(defaclacl::text[], ', ')
- FROM pg_default_acl
- WHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid
- ), ', ')) AS typeacl,
- (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE
sl1.objoid=nsp.oid) AS seclabels
+ '' AS typeacl
FROM
pg_namespace nsp
LEFT OUTER JOIN pg_description des ON
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
index e01f7b7b..0442eed9 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
@@ -733,6 +733,7 @@ class TableView(BaseTableView, DataTypeReader,
VacuumSettings):
"/".join([self.table_template_path, 'get_inherits.sql']),
show_system_objects=self.blueprint.show_system_objects,
tid=tid,
+ scid=scid,
server_type=self.manager.server_type
)
status, rset = self.conn.execute_2darray(SQL)
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py
index f7b99100..d316afd4 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py
@@ -16,6 +16,7 @@ import pgadmin.browser.server_groups.servers.databases as
database
from flask import render_template, request, jsonify
from flask_babel import gettext
from pgadmin.browser.collection import CollectionNodeModule
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.partitions
import backend_supported
from pgadmin.browser.utils import PGChildNodeView
from pgadmin.utils.ajax import make_json_response, internal_server_error, \
make_response as ajax_response, gone
@@ -74,17 +75,7 @@ class IndexesModule(CollectionNodeModule):
# In case of partitioned table return false.
if 'tid' in kwargs and manager.version >= 100000:
- partition_path = 'partition/sql/#{0}#'.format(manager.version)
- SQL = render_template(
- "/".join([partition_path, 'backend_support.sql']),
- tid=kwargs['tid']
- )
- status, res = conn.execute_scalar(SQL)
-
- # check if any errors
- if not status:
- return internal_server_error(errormsg=res)
- return not res
+ return backend_supported(self, manager, **kwargs)
if 'vid' not in kwargs:
return True
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py
index 69450d45..7d6c0a97 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py
@@ -26,6 +26,21 @@ from config import PG_DEFAULT_DRIVER
from pgadmin.browser.utils import PGChildModule
+def backend_supported(module, manager, **kwargs):
+ if 'tid' in kwargs and CollectionNodeModule.BackendSupported(module,
manager, **kwargs):
+ conn = manager.connection(did=kwargs['did'])
+
+ template_path =
'partition/sql/{0}/#{0}#{1}#'.format(manager.server_type, manager.version)
+ SQL = render_template("/".join(
+ [template_path, 'backend_support.sql']), tid=kwargs['tid'])
+ status, res = conn.execute_scalar(SQL)
+
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return res
+
class PartitionsModule(CollectionNodeModule):
"""
class PartitionsModule(CollectionNodeModule)
@@ -88,21 +103,7 @@ class PartitionsModule(CollectionNodeModule):
"""
Load this module if it is a partition table
"""
- if manager.server_type == 'gpdb':
- return False
- if 'tid' in kwargs and CollectionNodeModule.BackendSupported(self,
manager, **kwargs):
- conn = manager.connection(did=kwargs['did'])
-
- template_path = 'partition/sql/#{0}#'.format(manager.version)
- SQL = render_template("/".join(
- [template_path, 'backend_support.sql']), tid=kwargs['tid'])
- status, res = conn.execute_scalar(SQL)
-
- # check if any errors
- if not status:
- return internal_server_error(errormsg=res)
-
- return res
+ return backend_supported(self, manager, **kwargs)
def register(self, app, options, first_registration=False):
"""
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/tests/__init__.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/tests/__init__.py
new file mode 100644
index 00000000..e69de29b
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/tests/test_backend_supported.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/tests/test_backend_supported.py
new file mode 100644
index 00000000..a7f3c791
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/tests/test_backend_supported.py
@@ -0,0 +1,113 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import sys
+
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.partitions
import PartitionsModule
+from pgadmin.utils.route import BaseTestGenerator
+
+if sys.version_info < (3, 3):
+ from mock import patch, Mock, call
+else:
+ from unittest.mock import patch, Mock, call
+
+
+class TestBackendSupport(BaseTestGenerator):
+ scenarios = [
+ ('when tid is not present in arguments, should return None and no
query should be done',
+ dict(
+ manager=dict(
+ server_type="",
+ version=""
+ ),
+ input_arguments=dict(did=432),
+
+ collection_node_active=True,
+ connection_execution_return_value=[],
+
+ expected_return_value=None,
+ expect_error_response=False,
+ expected_number_calls_on_render_template=0
+ )),
+ ('when tid is present in arguments and CollectionNodeModule does not
support, '
+ 'should return None and no query should be done',
+ dict(
+ manager=dict(
+ server_type="",
+ version=""
+ ),
+ input_arguments=dict(did=432, tid=123),
+
+ collection_node_active=False,
+ connection_execution_return_value=[],
+
+ expected_return_value=None,
+ expect_error_response=False,
+ expected_number_calls_on_render_template=0
+ )),
+ ('when table is partitioned, '
+ 'should return the table identifier',
+ dict(
+ manager=dict(
+ server_type="gpdb",
+ version="5"
+ ),
+ input_arguments=dict(did=432, tid=123),
+
+ collection_node_active=True,
+ connection_execution_return_value=[True, 123],
+
+ expected_return_value=123,
+ expect_error_response=False,
+ expected_number_calls_on_render_template=1,
+
expect_render_template_to_be_called_with=call('partition/sql/gpdb/#gpdb#5#/backend_support.sql',
tid=123)
+ )),
+ ('when error happens while querying the database, '
+ 'should return an internal server error',
+ dict(
+ manager=dict(
+ server_type="pg",
+ version="10"
+ ),
+ input_arguments=dict(did=432, tid=123),
+
+ collection_node_active=True,
+ connection_execution_return_value=[False, "Some ugly error"],
+
+ expected_return_value=None,
+ expect_error_response=True,
+ expected_number_calls_on_render_template=1,
+
expect_render_template_to_be_called_with=call('partition/sql/pg/#pg#10#/backend_support.sql',
tid=123)
+ ))
+ ]
+
+
@patch('pgadmin.browser.server_groups.servers.databases.schemas.tables.partitions.internal_server_error')
+
@patch('pgadmin.browser.server_groups.servers.databases.schemas.tables.partitions.CollectionNodeModule')
+
@patch('pgadmin.browser.server_groups.servers.databases.schemas.tables.partitions.render_template')
+ def runTest(self, render_template_mock, CollectionNodeModule_mock,
internal_server_error_mock):
+ module = PartitionsModule("partition")
+ module.manager = Mock()
+ module.manager.server_type = self.manager['server_type']
+ module.manager.version = self.manager['version']
+ connection_mock = Mock()
+ connection_mock.execute_scalar.return_value =
self.connection_execution_return_value
+ module.manager.connection.return_value = connection_mock
+ CollectionNodeModule_mock.BackendSupported.return_value =
self.collection_node_active
+
+ result = module.BackendSupported(module.manager,
**self.input_arguments)
+
+ if self.expected_number_calls_on_render_template == 0:
+ render_template_mock.assert_not_called()
+ else:
+
render_template_mock.assert_has_calls([self.expect_render_template_to_be_called_with])
+
+ if self.expect_error_response:
+
internal_server_error_mock.assert_called_with(errormsg=self.connection_execution_return_value[1])
+ else:
+ self.assertEqual(result, self.expected_return_value)
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/get_oid_with_transaction.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/get_oid_with_transaction.sql
new file mode 100644
index 00000000..13f80955
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/get_oid_with_transaction.sql
@@ -0,0 +1,6 @@
+SELECT ct.conindid AS oid,
+ ct.conname AS name,
+ NOT convalidated AS convalidated
+FROM pg_constraint ct
+WHERE contype='x' AND
+ conrelid = {{tid}}::oid LIMIT 1;
\ No newline at end of file
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/nodes.sql
new file mode 100644
index 00000000..70cb041a
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/nodes.sql
@@ -0,0 +1,10 @@
+SELECT conindid as oid,
+ conname as name,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+WHERE contype='x' AND
+ conrelid = {{tid}}::oid
+{% if exid %}
+ AND conindid = {{exid}}::oid
+{% endif %}
+ORDER BY conname
\ No newline at end of file
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/get_oid_with_transaction.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/get_oid_with_transaction.sql
index 13f80955..1b2b7279 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/get_oid_with_transaction.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/get_oid_with_transaction.sql
@@ -1,6 +1,6 @@
SELECT ct.conindid AS oid,
ct.conname AS name,
- NOT convalidated AS convalidated
+ true AS convalidated
FROM pg_constraint ct
WHERE contype='x' AND
- conrelid = {{tid}}::oid LIMIT 1;
\ No newline at end of file
+ conrelid = {{tid}}::oid LIMIT 1;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/nodes.sql
index 70cb041a..8f0ab412 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/nodes.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/nodes.sql
@@ -1,10 +1,3 @@
-SELECT conindid as oid,
- conname as name,
- NOT convalidated as convalidated
+SELECT '' AS oid, '' AS conname, '' AS convalidated
FROM pg_constraint ct
-WHERE contype='x' AND
- conrelid = {{tid}}::oid
-{% if exid %}
- AND conindid = {{exid}}::oid
-{% endif %}
-ORDER BY conname
\ No newline at end of file
+WHERE false
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/get_oid.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/get_oid.sql
new file mode 100644
index 00000000..576c9761
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/get_oid.sql
@@ -0,0 +1,5 @@
+SELECT ct.oid,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+WHERE contype='f' AND
+ct.conname = {{ name|qtLiteral }};
\ No newline at end of file
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/get_oid_with_transaction.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/get_oid_with_transaction.sql
new file mode 100644
index 00000000..abe636a1
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/get_oid_with_transaction.sql
@@ -0,0 +1,6 @@
+SELECT ct.oid,
+ ct.conname as name,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+WHERE contype='f' AND
+ conrelid = {{tid}}::oid LIMIT 1;
\ No newline at end of file
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/nodes.sql
new file mode 100644
index 00000000..be54e8cb
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/nodes.sql
@@ -0,0 +1,7 @@
+SELECT ct.oid,
+ conname as name,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+WHERE contype='f' AND
+ conrelid = {{tid}}::oid
+ORDER BY conname
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid.sql
index 576c9761..20d84278 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid.sql
@@ -1,5 +1,5 @@
SELECT ct.oid,
- NOT convalidated as convalidated
+ true as convalidated
FROM pg_constraint ct
WHERE contype='f' AND
-ct.conname = {{ name|qtLiteral }};
\ No newline at end of file
+ct.conname = {{ name|qtLiteral }};
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid_with_transaction.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid_with_transaction.sql
index abe636a1..b3728a53 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid_with_transaction.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/get_oid_with_transaction.sql
@@ -1,6 +1,6 @@
SELECT ct.oid,
ct.conname as name,
- NOT convalidated as convalidated
+ true as convalidated
FROM pg_constraint ct
WHERE contype='f' AND
- conrelid = {{tid}}::oid LIMIT 1;
\ No newline at end of file
+ conrelid = {{tid}}::oid LIMIT 1;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/nodes.sql
index be54e8cb..8ca7dc56 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/nodes.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/nodes.sql
@@ -1,6 +1,6 @@
SELECT ct.oid,
conname as name,
- NOT convalidated as convalidated
+ true as convalidated
FROM pg_constraint ct
WHERE contype='f' AND
conrelid = {{tid}}::oid
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/attach.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/attach.sql
similarity index 100%
rename from
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/attach.sql
rename to
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/attach.sql
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/backend_support.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/backend_support.sql
new file mode 100644
index 00000000..119ba668
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/backend_support.sql
@@ -0,0 +1,9 @@
+SELECT CASE WHEN number_of_rows > 0
+ THEN TRUE
+ ELSE FALSE END AS ptable
+FROM (
+ SELECT count(*) AS number_of_rows
+ FROM pg_class
+ INNER JOIN pg_partitions ON relname = tablename
+ WHERE pg_class.oid = {{ tid }}::oid
+ ) AS number_of_partitions
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/create.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/create.sql
similarity index 100%
rename from
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/create.sql
rename to
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/create.sql
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/detach.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/detach.sql
similarity index 100%
rename from
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/detach.sql
rename to
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/detach.sql
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/get_attach_tables.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/get_attach_tables.sql
similarity index 100%
rename from
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/get_attach_tables.sql
rename to
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/get_attach_tables.sql
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/nodes.sql
new file mode 100644
index 00000000..f8c1c51b
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/nodes.sql
@@ -0,0 +1,39 @@
+SELECT
+ table_class.oid,
+ partitions.partitiontablename
AS name,
+ (SELECT count(*)
+ FROM pg_trigger
+ WHERE tgrelid = table_class.oid AND tgisconstraint = FALSE)
AS triggercount,
+ (SELECT count(*)
+ FROM pg_trigger
+ WHERE tgrelid = table_class.oid AND tgisconstraint = FALSE AND tgenabled =
'O') AS has_enable_triggers,
+ partitions.partitionboundary
AS partition_value,
+ partitions.partitionschemaname
AS schema_id,
+ schema_name,
+ CASE WHEN sub_partitions.n > 0
+ THEN TRUE
+ ELSE FALSE END
is_partitioned,
+ ''
AS partition_scheme
+FROM
+ (SELECT
+ table_class.relnamespace,
+ nsp.nspname AS schema_name,
+ partitions.partitiontablename,
+ partitions.partitionboundary,
+ partitions.partitionschemaname
+ FROM pg_class table_class
+ INNER JOIN pg_partitions partitions
+ ON (relname = tablename AND parentpartitiontablename IS NULL) OR
relname = parentpartitiontablename
+ LEFT JOIN pg_namespace nsp ON table_class.relnamespace = nsp.oid
+ WHERE
+ {% if ptid %} table_class.oid = {{ ptid }}::OID {% endif %}
+ {% if not ptid %} table_class.oid = {{ tid }}::OID {% endif %}
+ ) AS partitions
+ LEFT JOIN (SELECT
+ parentpartitiontablename,
+ count(*) AS n
+ FROM pg_partitions
+ GROUP BY parentpartitiontablename) sub_partitions
+ ON partitions.partitiontablename = sub_partitions.parentpartitiontablename
+ LEFT JOIN pg_class table_class ON partitions.relnamespace =
table_class.relnamespace AND partitions.partitiontablename = table_class.relname
+ORDER BY partitions.partitiontablename;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/properties.sql
new file mode 100644
index 00000000..c39829af
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/gpdb/5_plus/properties.sql
@@ -0,0 +1,85 @@
+SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS
relacl_str,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE
+ (SELECT sp.spcname FROM pg_database dtb
+ JOIN pg_tablespace sp ON dtb.dattablespace=sp.oid
+ WHERE dtb.oid = {{ did }}::oid)
+ END) as spcname,
+ (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as
parent_schema,
+ nsp.nspname as schema,
+ pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids,
+ (CASE WHEN partitions.number_of_partitions > 0 THEN true ELSE false END) AS
relispartition,
+ rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname,
con.conkey,
+ EXISTS(select 1 FROM pg_trigger
+ JOIN pg_proc pt ON pt.oid=tgfoid AND
pt.proname='logtrigger'
+ JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND
pc.proname='slonyversion'
+ WHERE tgrelid=rel.oid) AS isrepl,
+ (SELECT count(*)
+ FROM pg_trigger
+ WHERE tgrelid = rel.oid AND tgisconstraint = FALSE) AS triggercount,
+ (SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
+ quote_ident(nspname)||'.'||quote_ident(c.relname)
+ ELSE quote_ident(c.relname) END AS inherited_tables
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
+ (SELECT count(*)
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
+ false AS relpersistence,
+ substring(array_to_string(rel.reloptions, ',') FROM
'fillfactor=([0-9]*)') AS fillfactor,
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS autovacuum_enabled,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS
autovacuum_vacuum_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS
autovacuum_analyze_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS toast_autovacuum_enabled,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS
toast_autovacuum_vacuum_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS
toast_autovacuum_analyze_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
+ array_to_string(rel.reloptions, ',') AS table_vacuum_settings_str,
+ array_to_string(tst.reloptions, ',') AS toast_table_vacuum_settings_str,
+ rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions,
rel.reltype, typ.typname,
+ (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS
hastoasttable,
+ -- Added for pgAdmin4
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)'))::boolean THEN true ELSE false END) AS
autovacuum_custom,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)'))::boolean AND rel.reltoastrelid != 0 THEN
true ELSE false END) AS toast_autovacuum,
+ NULL AS seclabels,
+ (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END)
AS is_sys_table,
+ -- Added for partition table
+ (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS
is_partitioned,
+ '' AS partition_scheme,
+ {% if ptid %}
+ (CASE WHEN partitions.number_of_partitions > 0 THEN
partitions.expression ELSE '' END) AS partition_value,
+ (SELECT relname FROM pg_class WHERE oid = {{ tid }}::oid) AS
partitioned_table_name
+ {% else %}
+ partitions.expression AS partition_value
+ {% endif %}
+
+FROM pg_class rel
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0
AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
+ LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
+ LEFT JOIN pg_type typ ON rel.reltype=typ.oid
+ LEFT JOIN pg_inherits inh ON inh.inhrelid = rel.oid
+ LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+ LEFT JOIN (SELECT tablename, partitionboundary as expression, count(*)
number_of_partitions FROM pg_partitions GROUP BY tablename, expression)
partitions ON rel.relname = tablename
+WHERE inh.inhparent = {{ tid }}::oid
+{% if ptid %} AND rel.oid = {{ ptid }}::oid {% endif %}
+ORDER BY rel.relname;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/attach.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/attach.sql
new file mode 100644
index 00000000..0661d9cf
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/attach.sql
@@ -0,0 +1,2 @@
+ALTER TABLE {{conn|qtIdent(data.parent_schema, data.partitioned_table_name)}}
ATTACH PARTITION {{conn|qtIdent(data.schema, data.name)}}
+ {{ data.partition_value }};
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/backend_support.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/backend_support.sql
similarity index 100%
rename from
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/backend_support.sql
rename to
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/backend_support.sql
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/create.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/create.sql
new file mode 100644
index 00000000..19eea8cd
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/create.sql
@@ -0,0 +1,36 @@
+{% import 'table/sql/macros/constraints.macro' as CONSTRAINTS %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE STARTS HERE ======#}
+{#===========================================#}
+{### CREATE TABLE STATEMENT FOR partitions ###}
+CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE
{{conn|qtIdent(data.schema, data.name)}}{% if data.relispartition is defined
and data.relispartition %} PARTITION OF {{conn|qtIdent(data.parent_schema,
data.partitioned_table_name)}}{% endif %}
+
+{# Macro to render for constraints #}
+{% if data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 or data.check_constraint|length > 0 or
data.exclude_constraint|length > 0 %}
+( {% endif %}
+{% if data.primary_key|length > 0 %}{{CONSTRAINTS.PRIMARY_KEY(conn,
data.primary_key[0])}}{% endif %}{% if data.unique_constraint|length > 0 %}{%
if data.primary_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.UNIQUE(conn, data.unique_constraint)}}{% endif %}{% if
data.foreign_key|length > 0 %}{% if data.primary_key|length > 0 or
data.unique_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.FOREIGN_KEY(conn, data.foreign_key)}}{% endif %}{% if
data.check_constraint|length > 0 %}{% if data.primary_key|length > 0 or
data.unique_constraint|length > 0 or data.foreign_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.CHECK(conn, data.check_constraint)}}{% endif %}{% if
data.exclude_constraint|length > 0 %}{% if data.primary_key|length > 0 or
data.unique_constraint|length > 0 or data.foreign_key|length > 0 or
data.check_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.EXCLUDE(conn, data.exclude_constraint)}}{% endif %}
+{% if data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 or data.check_constraint|length > 0 or
data.exclude_constraint|length > 0 %}
+
+)
+{% endif %}
+ {{ data.partition_value }}{% if data.is_partitioned is defined and
data.is_partitioned %}
+
+ PARTITION BY {{ data.partition_scheme }}{% endif %}
+{### SQL for Tablespace ###}
+{% if data.spcname %}
+
+TABLESPACE {{ conn|qtIdent(data.spcname) }};
+{% else %}
+;
+
+{% endif %}
+{### Alter SQL for Owner ###}
+{% if data.relowner %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ OWNER to {{conn|qtIdent(data.relowner)}};
+{% endif %}
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/detach.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/detach.sql
new file mode 100644
index 00000000..4b413db1
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/detach.sql
@@ -0,0 +1 @@
+ALTER TABLE {{conn|qtIdent(data.parent_schema, data.partitioned_table_name)}}
DETACH PARTITION {{conn|qtIdent(data.schema, data.name)}};
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/get_attach_tables.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/get_attach_tables.sql
new file mode 100644
index 00000000..dccda321
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/get_attach_tables.sql
@@ -0,0 +1,23 @@
+SELECT oid, quote_ident(nspname)||'.'||quote_ident(relname) AS table_name FROM
+(SELECT
+ r.oid, r.relname, n.nspname, array_agg(a.attname) attnames,
array_agg(a.atttypid) atttypes
+FROM
+ (SELECT oid, relname, relnamespace FROM pg_catalog.pg_class
+ WHERE relkind in ('r', 'p') AND NOT relispartition) r
+ JOIN (SELECT oid AS nspoid, nspname FROM
+ pg_catalog.pg_namespace WHERE nspname NOT LIKE 'pg\_%') n
+ ON (r.relnamespace = n.nspoid)
+ JOIN (SELECT attrelid, attname, atttypid FROM
+ pg_catalog.pg_attribute WHERE attnum > 0 ORDER BY attrelid, attnum) a
+ ON (r.oid = a.attrelid)
+GROUP BY r.oid, r.relname, r.relnamespace, n.nspname) all_tables
+JOIN
+(SELECT
+ attrelid, array_agg(attname) attnames, array_agg(atttypid) atttypes
+FROM
+ (SELECT * FROM pg_catalog.pg_attribute
+ WHERE attrelid = {{ tid }} AND attnum > 0
+ ORDER BY attrelid, attnum) attributes
+GROUP BY attrelid) current_table ON current_table.attrelid != all_tables.oid
+ AND current_table.attnames = all_tables.attnames
+ AND current_table.atttypes = all_tables.atttypes
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/nodes.sql
similarity index 100%
rename from
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/nodes.sql
rename to
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/nodes.sql
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/properties.sql
similarity index 100%
rename from
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/10_plus/properties.sql
rename to
web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/pg/10_plus/properties.sql
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/attach.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/attach.sql
new file mode 100644
index 00000000..0661d9cf
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/attach.sql
@@ -0,0 +1,2 @@
+ALTER TABLE {{conn|qtIdent(data.parent_schema, data.partitioned_table_name)}}
ATTACH PARTITION {{conn|qtIdent(data.schema, data.name)}}
+ {{ data.partition_value }};
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/backend_support.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/backend_support.sql
new file mode 100644
index 00000000..5fa1d7e5
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/backend_support.sql
@@ -0,0 +1,9 @@
+{#=============Checks if it is partitioned table========#}
+{% if tid %}
+SELECT
+ CASE WHEN c.relkind = 'p' THEN True ELSE False END As ptable
+FROM
+ pg_class c
+WHERE
+ c.oid = {{ tid }}::oid
+{% endif %}
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/create.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/create.sql
new file mode 100644
index 00000000..19eea8cd
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/create.sql
@@ -0,0 +1,36 @@
+{% import 'table/sql/macros/constraints.macro' as CONSTRAINTS %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE STARTS HERE ======#}
+{#===========================================#}
+{### CREATE TABLE STATEMENT FOR partitions ###}
+CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE
{{conn|qtIdent(data.schema, data.name)}}{% if data.relispartition is defined
and data.relispartition %} PARTITION OF {{conn|qtIdent(data.parent_schema,
data.partitioned_table_name)}}{% endif %}
+
+{# Macro to render for constraints #}
+{% if data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 or data.check_constraint|length > 0 or
data.exclude_constraint|length > 0 %}
+( {% endif %}
+{% if data.primary_key|length > 0 %}{{CONSTRAINTS.PRIMARY_KEY(conn,
data.primary_key[0])}}{% endif %}{% if data.unique_constraint|length > 0 %}{%
if data.primary_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.UNIQUE(conn, data.unique_constraint)}}{% endif %}{% if
data.foreign_key|length > 0 %}{% if data.primary_key|length > 0 or
data.unique_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.FOREIGN_KEY(conn, data.foreign_key)}}{% endif %}{% if
data.check_constraint|length > 0 %}{% if data.primary_key|length > 0 or
data.unique_constraint|length > 0 or data.foreign_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.CHECK(conn, data.check_constraint)}}{% endif %}{% if
data.exclude_constraint|length > 0 %}{% if data.primary_key|length > 0 or
data.unique_constraint|length > 0 or data.foreign_key|length > 0 or
data.check_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.EXCLUDE(conn, data.exclude_constraint)}}{% endif %}
+{% if data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 or data.check_constraint|length > 0 or
data.exclude_constraint|length > 0 %}
+
+)
+{% endif %}
+ {{ data.partition_value }}{% if data.is_partitioned is defined and
data.is_partitioned %}
+
+ PARTITION BY {{ data.partition_scheme }}{% endif %}
+{### SQL for Tablespace ###}
+{% if data.spcname %}
+
+TABLESPACE {{ conn|qtIdent(data.spcname) }};
+{% else %}
+;
+
+{% endif %}
+{### Alter SQL for Owner ###}
+{% if data.relowner %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ OWNER to {{conn|qtIdent(data.relowner)}};
+{% endif %}
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/detach.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/detach.sql
new file mode 100644
index 00000000..4b413db1
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/detach.sql
@@ -0,0 +1 @@
+ALTER TABLE {{conn|qtIdent(data.parent_schema, data.partitioned_table_name)}}
DETACH PARTITION {{conn|qtIdent(data.schema, data.name)}};
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/get_attach_tables.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/get_attach_tables.sql
new file mode 100644
index 00000000..dccda321
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/get_attach_tables.sql
@@ -0,0 +1,23 @@
+SELECT oid, quote_ident(nspname)||'.'||quote_ident(relname) AS table_name FROM
+(SELECT
+ r.oid, r.relname, n.nspname, array_agg(a.attname) attnames,
array_agg(a.atttypid) atttypes
+FROM
+ (SELECT oid, relname, relnamespace FROM pg_catalog.pg_class
+ WHERE relkind in ('r', 'p') AND NOT relispartition) r
+ JOIN (SELECT oid AS nspoid, nspname FROM
+ pg_catalog.pg_namespace WHERE nspname NOT LIKE 'pg\_%') n
+ ON (r.relnamespace = n.nspoid)
+ JOIN (SELECT attrelid, attname, atttypid FROM
+ pg_catalog.pg_attribute WHERE attnum > 0 ORDER BY attrelid, attnum) a
+ ON (r.oid = a.attrelid)
+GROUP BY r.oid, r.relname, r.relnamespace, n.nspname) all_tables
+JOIN
+(SELECT
+ attrelid, array_agg(attname) attnames, array_agg(atttypid) atttypes
+FROM
+ (SELECT * FROM pg_catalog.pg_attribute
+ WHERE attrelid = {{ tid }} AND attnum > 0
+ ORDER BY attrelid, attnum) attributes
+GROUP BY attrelid) current_table ON current_table.attrelid != all_tables.oid
+ AND current_table.attnames = all_tables.attnames
+ AND current_table.atttypes = all_tables.atttypes
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/nodes.sql
new file mode 100644
index 00000000..bd828cd7
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/nodes.sql
@@ -0,0 +1,15 @@
+SELECT rel.oid, rel.relname AS name,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid AND tgisinternal =
FALSE) AS triggercount,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid AND tgisinternal =
FALSE AND tgenabled = 'O') AS has_enable_triggers,
+ pg_get_expr(rel.relpartbound, rel.oid) AS partition_value,
+ rel.relnamespace AS schema_id,
+ nsp.nspname AS schema_name,
+ (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
+ (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef(rel.oid::oid) ELSE ''
END) AS partition_scheme
+FROM
+ (SELECT * FROM pg_inherits WHERE inhparent = {{ tid }}::oid) inh
+ LEFT JOIN pg_class rel ON inh.inhrelid = rel.oid
+ LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+ WHERE rel.relispartition
+ {% if ptid %} AND rel.oid = {{ ptid }}::OID {% endif %}
+ ORDER BY rel.relname;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/properties.sql
new file mode 100644
index 00000000..4b268573
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partition/sql/ppas/10_plus/properties.sql
@@ -0,0 +1,82 @@
+SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS
relacl_str,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE
+ (SELECT sp.spcname FROM pg_database dtb
+ JOIN pg_tablespace sp ON dtb.dattablespace=sp.oid
+ WHERE dtb.oid = {{ did }}::oid)
+ END) as spcname,
+ (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as
parent_schema,
+ nsp.nspname as schema,
+ pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids,
rel.relispartition,
+ rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname,
con.conkey,
+ EXISTS(select 1 FROM pg_trigger
+ JOIN pg_proc pt ON pt.oid=tgfoid AND
pt.proname='logtrigger'
+ JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND
pc.proname='slonyversion'
+ WHERE tgrelid=rel.oid) AS isrepl,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid AND tgisinternal
= FALSE) AS triggercount,
+ (SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
+ quote_ident(nspname)||'.'||quote_ident(c.relname)
+ ELSE quote_ident(c.relname) END AS inherited_tables
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
+ (SELECT count(*)
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
+ (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS
relpersistence,
+ substring(array_to_string(rel.reloptions, ',') FROM
'fillfactor=([0-9]*)') AS fillfactor,
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS autovacuum_enabled,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS
autovacuum_vacuum_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS
autovacuum_analyze_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
+ substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS toast_autovacuum_enabled,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS
toast_autovacuum_vacuum_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS
toast_autovacuum_analyze_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
+ substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
+ array_to_string(rel.reloptions, ',') AS table_vacuum_settings_str,
+ array_to_string(tst.reloptions, ',') AS toast_table_vacuum_settings_str,
+ rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions,
rel.reloftype, typ.typname,
+ (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS
hastoasttable,
+ -- Added for pgAdmin4
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)'))::boolean THEN true ELSE false END) AS
autovacuum_custom,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)'))::boolean AND rel.reltoastrelid != 0 THEN
true ELSE false END) AS toast_autovacuum,
+
+ (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE
sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
+ (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END)
AS is_sys_table,
+ -- Added for partition table
+ (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS
is_partitioned,
+ (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef(rel.oid::oid) ELSE
'' END) AS partition_scheme,
+ {% if ptid %}
+ (CASE WHEN rel.relispartition THEN pg_get_expr(rel.relpartbound, {{
ptid }}::oid) ELSE '' END) AS partition_value,
+ (SELECT relname FROM pg_class WHERE oid = {{ tid }}::oid) AS
partitioned_table_name
+ {% else %}
+ pg_get_expr(rel.relpartbound, rel.oid) AS partition_value
+ {% endif %}
+
+FROM pg_class rel
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0
AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
+ LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
+ LEFT JOIN pg_type typ ON rel.reloftype=typ.oid
+ LEFT JOIN pg_inherits inh ON inh.inhrelid = rel.oid
+ LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+WHERE rel.relispartition AND inh.inhparent = {{ tid }}::oid
+{% if ptid %} AND rel.oid = {{ ptid }}::oid {% endif %}
+ORDER BY rel.relname;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/get_inherits.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/get_inherits.sql
new file mode 100644
index 00000000..9d05c70c
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/get_inherits.sql
@@ -0,0 +1,17 @@
+{% import 'table/sql/macros/db_catalogs.macro' as CATALOG %}
+SELECT c.oid, c.relname , nspname,
+CASE WHEN nspname NOT LIKE 'pg\_%' THEN
+ quote_ident(nspname)||'.'||quote_ident(c.relname)
+ELSE quote_ident(c.relname)
+END AS inherits
+FROM pg_class c
+JOIN pg_namespace n
+ON n.oid=c.relnamespace
+WHERE relkind='r'
+{% if not show_system_objects %}
+{{ CATALOG.VALID_CATALOGS(server_type) }}
+{% endif %}
+{% if tid %}
+AND c.oid != tid
+{% endif %}
+ORDER BY relnamespace, c.relname
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/get_inherits.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/get_inherits.sql
index 9d05c70c..b640f5af 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/get_inherits.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/get_inherits.sql
@@ -14,4 +14,5 @@ WHERE relkind='r'
{% if tid %}
AND c.oid != tid
{% endif %}
+AND c.relnamespace = {{ scid }}
ORDER BY relnamespace, c.relname
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/nodes.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/nodes.sql
new file mode 100644
index 00000000..b0bc8423
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/nodes.sql
@@ -0,0 +1,10 @@
+SELECT rel.oid, rel.relname AS name,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid) AS triggercount,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid AND tgenabled =
'O') AS has_enable_triggers
+FROM pg_class rel
+ WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = {{ scid }}::oid
+ AND rel.relname NOT IN (SELECT partitiontablename FROM pg_partitions)
+ {% if tid %}
+ AND rel.oid = {{tid}}::OID
+ {% endif %}
+ ORDER BY rel.relname;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
index 842231b3..f152392e 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
@@ -116,7 +116,7 @@ class BaseTableView(PGChildNodeView):
if server_type == 'gpdb' else
'#{0}#'.format(ver)
)
- self.partition_template_path = 'partition/sql/#{0}#'.format(ver)
+ self.partition_template_path =
'partition/sql/{0}/#{0}#{1}#'.format(server_type, ver)
# Template for Column ,check constraint and exclusion
# constraint node
@@ -600,7 +600,8 @@ class BaseTableView(PGChildNodeView):
# system columns
SQL = render_template("/".join([self.table_template_path,
'get_inherits.sql']),
- show_system_objects=False
+ show_system_objects=False,
+ scid=scid
)
status, rset = self.conn.execute_2darray(SQL)
if not status:
diff --git
a/web/pgadmin/browser/server_groups/servers/tests/test_check_recovery.py
b/web/pgadmin/browser/server_groups/servers/tests/test_check_recovery.py
index b31fcc19..2ab2a6ad 100644
--- a/web/pgadmin/browser/server_groups/servers/tests/test_check_recovery.py
+++ b/web/pgadmin/browser/server_groups/servers/tests/test_check_recovery.py
@@ -22,7 +22,7 @@ class TestCheckRecovery(BaseTestGenerator):
if cursor is not None and cursor.connection is not None:
server_version = cursor.connection.server_version
if server_version >= 100000:
- version = '10_plus'
+ version = '5_plus'
elif server_version >= 90000:
version = '9.0_plus'
else:
diff --git a/web/pgadmin/utils/versioned_template_loader.py
b/web/pgadmin/utils/versioned_template_loader.py
index 088fc8a6..05f0dc73 100644
--- a/web/pgadmin/utils/versioned_template_loader.py
+++ b/web/pgadmin/utils/versioned_template_loader.py
@@ -29,6 +29,7 @@ class VersionedTemplateLoader(DispatchingJinjaLoader):
gpdb_versions = (
{'name': "gpdb_5.0_plus", 'number': 80323},
+ {'name': "5_plus", 'number': 80323},
{'name': "default", 'number': 0}
)
