[pgadmin-hackers] Patch on roles query for pgAdmin with a Greenplum database

[Guillaume Lelarge]

Hi Chuck,

As you sent some patches for pgAdmin to work better with Greenplum
database, I'm wondering if you could try this patch on 1.12 branch with
a Greenplum database. As I don't have one, it's not easy to check if it
works as I supposed.

The issue is explained here:
http://archives.postgresql.org/pgadmin-support/2010-08/msg00001.php. The
OP sees a lot of queries from pgAdmin, always the same and it seems
related to our roles query. I've changed the CreateObjects method so
that we didn't launch one query for each user. But I'm not sure it works.

Can you try it?

Thanks.

Regards.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

diff --git a/pgadmin/schema/pgRole.cpp b/pgadmin/schema/pgRole.cpp
index e1d9601..65e6e41 100644
--- a/pgadmin/schema/pgRole.cpp
+++ b/pgadmin/schema/pgRole.cpp
@@ -356,6 +356,7 @@ pgObject *pgRoleBaseFactory::CreateObjects(pgCollection *collection, ctlTree *br
 {
     pgRole *role=0;
     pgSet *roles=0;
+    wxString query;
 
     wxString tabname;
 
@@ -366,16 +367,29 @@ pgObject *pgRoleBaseFactory::CreateObjects(pgCollection *collection, ctlTree *br
 
     // In 8.5+, role config options are in pg_db_role_setting
     if (collection->GetServer()->GetConnection()->BackendMinimumVersion(8, 5))
-        roles = collection->GetServer()->ExecuteSet(wxT("SELECT tab.oid, tab.*, pg_catalog.shobj_description(tab.oid, 'pg_authid') AS description, setting.setconfig AS rolconfig FROM ") +
-              tabname + wxT(" tab") +
+    {
+        query = wxT("SELECT tab.oid, tab.*, pg_catalog.shobj_description(tab.oid, 'pg_authid') AS description, setting.setconfig AS rolconfig");
+        if (collection->GetServer()->GetConnection()->GetIsGreenplum())
+            query += wxT(", (SELECT rsqname FROM pg_resqueue WHERE pg_resqueue.oid = rolresqueue) AS rsqname");
+        query += wxT(" FROM ") + tabname + wxT(" tab") +
               wxT("  LEFT OUTER JOIN pg_db_role_setting setting ON (tab.oid=setting.setrole AND setting.setdatabase=0)\n") + 
-              restriction +  wxT(" ORDER BY rolname"));
+              restriction +  wxT(" ORDER BY rolname");
+    }
     else if (collection->GetServer()->GetConnection()->BackendMinimumVersion(8, 2))
-        roles = collection->GetServer()->ExecuteSet(wxT("SELECT oid, *, pg_catalog.shobj_description(oid, 'pg_authid') AS description FROM ") 
-              + tabname + restriction + wxT(" ORDER BY rolname"));
+    {
+        query = wxT("SELECT oid, *, pg_catalog.shobj_description(oid, 'pg_authid') AS description ");
+        if (collection->GetServer()->GetConnection()->GetIsGreenplum())
+            query += wxT(", (SELECT rsqname FROM pg_resqueue WHERE pg_resqueue.oid = rolresqueue) AS rsqname");
+        query += wxT(" FROM ") + tabname + restriction + wxT(" ORDER BY rolname");
+    }
     else
-        roles = collection->GetServer()->ExecuteSet(wxT("SELECT oid, *, '' AS description FROM ") 
-             + tabname + restriction + wxT(" ORDER BY rolname"));
+    {
+        query = wxT("SELECT oid, *, '' AS description ") ;
+        if (collection->GetServer()->GetConnection()->GetIsGreenplum())
+            query += wxT(", (SELECT rsqname FROM pg_resqueue WHERE pg_resqueue.oid = rolresqueue) AS rsqname");
+        query += wxT(" FROM ") + tabname + restriction + wxT(" ORDER BY rolname");
+    }
+    roles = collection->GetServer()->ExecuteSet(query);
 
     if (roles)
     {
@@ -400,14 +414,9 @@ pgObject *pgRoleBaseFactory::CreateObjects(pgCollection *collection, ctlTree *br
 
             if (collection->GetServer()->GetConnection()->GetIsGreenplum())
             {
-                Oid rolresqueue = roles->GetOid(wxT("rolresqueue"));
-                if (rolresqueue != 0)
-                {
-                    role->iSetRolQueueName(collection->GetServer()->ExecuteScalar(wxT("SELECT rsqname FROM pg_resqueue WHERE pg_resqueue.oid = ") + roles->GetVal(wxT("rolresqueue"))));	
-                }
+                role->iSetRolQueueName(roles->GetVal(wxT("rsqname")));	
             }
 
-
             wxString cfg=roles->GetVal(wxT("rolconfig"));
             if (!cfg.IsEmpty())
                 FillArray(role->GetConfigList(), cfg.Mid(1, cfg.Length()-2));

-- 
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers