public inbox for [email protected]
help / color / mirror / Atom feedFrom: Joao De Almeida Pereira <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgadmin4][patch][GreenPlum] Display SQL for tables takes 15 minutes to execute
Date: Mon, 11 Jun 2018 16:20:01 -0400
Message-ID: <CAE+jja=iErGtDLO=ekGNOcA9b90B4fzXaf-u-SCRz1LxMPgadw@mail.gmail.com> (raw)
Hi Hackers,
Attached you can find the diff file that corrects RM 3415
<https://redmine.postgresql.org/issues/3415;
Thanks
Joao
Attachments:
[application/octet-stream] RM_3415.diff (16.7K, 3-RM_3415.diff)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/foreign_servers/templates/foreign_servers/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/foreign_servers/templates/foreign_servers/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..bfe66f150
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/foreign_servers/templates/foreign_servers/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'datacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..bfe66f150
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/foreign_data_wrappers/templates/foreign_data_wrappers/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'datacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/languages/templates/languages/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/languages/templates/languages/sql/gpdb_5.0_plus/acl.sql
index ef55a511b..292644b91 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/languages/templates/languages/sql/gpdb_5.0_plus/acl.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/languages/templates/languages/sql/gpdb_5.0_plus/acl.sql
@@ -1,34 +1,7 @@
-SELECT 'lanacl' as deftype, COALESCE(gt.rolname, 'PUBLIC') grantee, g.rolname grantor,
- array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable
-FROM
- (SELECT
- d.grantee, d.grantor, d.is_grantable,
- CASE d.privilege_type
- WHEN 'USAGE' THEN 'U'
- ELSE 'UNKNOWN'
- END AS privilege_type
- FROM
- (SELECT lanacl FROM pg_language lan
- LEFT OUTER JOIN pg_shdescription descr ON (lan.oid=descr.objoid AND descr.classoid='pg_language'::regclass)
- WHERE lan.oid = {{ lid|qtLiteral }}::OID
- ) acl,
- (SELECT
- u_grantor.oid AS grantor,
- grantee.oid AS grantee,
- pr.type AS privilege_type,
- aclcontains(lan1.lanacl, makeaclitem(grantee.oid, u_grantor.oid, pr.type, true)) AS is_grantable
- FROM pg_language lan1, 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 'USAGE') pr(type)
- WHERE aclcontains(lan1.lanacl, 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 lan1.oid = {{ lid|qtLiteral }}::OID
- ) d
- ) d
-LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
-LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
-GROUP BY g.rolname, gt.rolname
+SELECT
+ 'lanacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..bfe66f150
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'datacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
index 1162ee677..2e2a781ba 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
@@ -1,38 +1,6 @@
SELECT
- COALESCE(gt.rolname, 'PUBLIC') AS grantee,
- g.rolname AS grantor, array_agg(privilege_type) AS privileges,
- array_agg(is_grantable) AS grantable
-FROM
- (SELECT
- (d).grantee AS grantee,
- (d).grantor AS grantor,
- (d).is_grantable AS is_grantable,
- CASE (d).privilege_type
- WHEN 'EXECUTE' THEN 'X'
- ELSE 'UNKNOWN' END AS privilege_type
- FROM
- (SELECT
- u_grantor.oid AS grantor,
- grantee.oid AS grantee,
- pr.type AS privilege_type,
- aclcontains(c.proacl, makeaclitem(grantee.oid, u_grantor.oid, pr.type, true)) AS is_grantable
- FROM pg_proc c, 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 'EXECUTE') pr(type)
- WHERE c.pronamespace = nc.oid
- AND (
- c.proacl is NULL
- OR aclcontains(c.proacl, 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 c.oid = {{ fnid }}::OID
- ) d
- ) d
- LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
- LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
-GROUP BY g.rolname, gt.rolname;
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/sequences/templates/sequence/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/sequences/templates/sequence/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..167184011
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/sequences/templates/sequence/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'relacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..f77d1ed9e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'attacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..167184011
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'relacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/gpdb_5.0_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/gpdb_5.0_plus/sql/acl.sql
index c50f9b2ab..48dcf1fe0 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/gpdb_5.0_plus/sql/acl.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/gpdb_5.0_plus/sql/acl.sql
@@ -1,38 +1,7 @@
-{# Fetch privileges for schema #}
SELECT
- 'nspacl' as deftype, COALESCE(gt.rolname, 'PUBLIC') AS grantee,
- g.rolname AS grantor, array_agg(b.privilege_type) AS privileges,
- array_agg(b.is_grantable) AS grantable
-FROM
- (SELECT
- (d).grantee AS grantee, (d).grantor AS grantor,
- (d).is_grantable AS is_grantable,
- CASE (d).privilege_type
- WHEN 'CREATE' THEN 'C'
- WHEN 'USAGE' THEN 'U'
- ELSE 'UNKNOWN - ' || (d).privilege_type
- END AS privilege_type
- FROM
- (
- 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 nc.oid = {{ scid|qtLiteral }}::OID
- ) a
- ) b
- LEFT JOIN pg_catalog.pg_roles g ON (b.grantor = g.oid)
- LEFT JOIN pg_catalog.pg_roles gt ON (b.grantee = gt.oid)
-GROUP BY g.rolname, gt.rolname;
+ 'nspacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
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 d917b8ae9..48dcf1fe0 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
@@ -1,38 +1,7 @@
-{# Fetch privileges for schema #}
SELECT
- 'nspacl' as deftype, COALESCE(gt.rolname, 'PUBLIC') AS grantee,
- g.rolname AS grantor, array_agg(b.privilege_type) AS privileges,
- array_agg(b.is_grantable) AS grantable
-FROM
- (SELECT
- (a).grantee AS grantee, (a).grantor AS grantor,
- (a).is_grantable AS is_grantable,
- CASE (a).privilege_type
- WHEN 'CREATE' THEN 'C'
- WHEN 'USAGE' THEN 'U'
- ELSE 'UNKNOWN - ' || (a).privilege_type
- END AS privilege_type
- FROM
- (
- 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 nc.oid = {{ scid|qtLiteral }}::OID
- ) a
- ) b
- LEFT JOIN pg_catalog.pg_roles g ON (b.grantor = g.oid)
- LEFT JOIN pg_catalog.pg_roles gt ON (b.grantee = gt.oid)
-GROUP BY g.rolname, gt.rolname;
+ 'nspacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/gpdb_5.0_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/gpdb_5.0_plus/sql/acl.sql
index 64ed13b40..7a87c27ec 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/gpdb_5.0_plus/sql/acl.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/gpdb_5.0_plus/sql/acl.sql
@@ -1,74 +1,8 @@
{# ============================ Get ACLs ========================= #}
-{% if vid %}
SELECT
- 'datacl' as deftype,
- COALESCE(gt.rolname, 'PUBLIC') grantee,
- g.rolname grantor,
- array_agg(privilege_type) as privileges,
- array_agg(is_grantable) as grantable
-FROM
- (SELECT
- d.grantee, d.grantor, d.is_grantable,
- CASE d.privilege_type
- WHEN 'DELETE' THEN 'd'
- WHEN 'INSERT' THEN 'a'
- WHEN 'REFERENCES' THEN 'x'
- WHEN 'SELECT' THEN 'r'
- WHEN 'TRIGGER' THEN 't'
- WHEN 'UPDATE' THEN 'w'
- WHEN 'TRUNCATE' THEN 'D'
- ELSE 'UNKNOWN'
- END AS privilege_type
- FROM
- (SELECT
- relacl
- FROM
- pg_class cl
- LEFT OUTER JOIN pg_shdescription descr ON (
- cl.oid=descr.objoid AND descr.classoid='pg_class'::regclass)
- WHERE
- cl.oid = {{ vid }}::OID AND relkind = 'v'
- ) acl,
- (SELECT
- (d).grantee AS grantee,
- (d).grantor AS grantor,
- (d).is_grantable AS is_grantable,
- (d).privilege_type AS privilege_type
- FROM
- (SELECT
- u_grantor.oid AS grantor,
- grantee.oid AS grantee,
- pr.type AS privilege_type,
- aclcontains(c.relacl, makeaclitem(grantee.oid, u_grantor.oid, pr.type, true)) AS is_grantable
- FROM pg_class c, 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 'SELECT'
- UNION ALL
- SELECT 'INSERT'
- UNION ALL
- SELECT 'UPDATE'
- UNION ALL
- SELECT 'DELETE'
- UNION ALL
- SELECT 'TRUNCATE'
- UNION ALL
- SELECT 'REFERENCES'
- UNION ALL
- SELECT 'TRIGGER') pr(type)
- WHERE c.relnamespace = nc.oid
- AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"]))
- AND aclcontains(c.relacl, 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 c.oid = {{ vid }}
- ) d
- ) d
- ) d
- LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
- LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
-GROUP BY
- g.rolname, gt.rolname
-{% endif %}
+ 'datacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..bfe66f150
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'datacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/tablespaces/templates/tablespaces/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/tablespaces/templates/tablespaces/sql/gpdb_5.0_plus/acl.sql
new file mode 100644
index 000000000..54aa620bf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/tablespaces/templates/tablespaces/sql/gpdb_5.0_plus/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'spcacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [pgadmin4][patch][GreenPlum] Display SQL for tables takes 15 minutes to execute
In-Reply-To: <CAE+jja=iErGtDLO=ekGNOcA9b90B4fzXaf-u-SCRz1LxMPgadw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox