Subject: PATCH: To fix the issue with stats table in PG9.6 (pgAdmin4)

From: Murtuza Zabuawala <[email-masked]>
Date: Thu, 22 Sep 2016 12:50:07 +0530

Hi,

PFA patch to fix the issue where pgAdmin4 was throwing error when user
clicks on Statistics tab when using PG9.6.
RM#1719

*Issue:*
The column `waiting` has been removed from `pg_stat_activity` View instead
two new columns has been added `wait_event` and `wait_event_type`.

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/web/pgadmin/browser/server_groups/servers/__init__.py b/web/pgadmin/browser/server_groups/servers/__init__.py
index 0b6c6ca..24859e8 100644
--- a/web/pgadmin/browser/server_groups/servers/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/__init__.py
@@ -662,17 +662,26 @@ class ServerNode(PGChildNodeView):
     def modified_sql(self, gid, sid):
         return make_json_response(data='')
 
+    def get_template_directory(self, version):
+        """ This function will check and return template directory
+        based on postgres verion"""
+        if version >= 90600:
+            return '9.6_plus'
+        elif version >= 90200:
+            return '9.2_plus'
+        else:
+            return '9.1_plus'
+
     def statistics(self, gid, sid):
         from pgadmin.utils.driver import get_driver
         manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
         conn = manager.connection()
-
         if conn.connected():
             status, res = conn.execute_dict(
                 render_template(
                     "/".join([
                         'servers/sql',
-                        '9.2_plus' if manager.version >= 90200 else '9.1_plus',
+                        self.get_template_directory(manager.version),
                         'stats.sql'
                     ]),
                     conn=conn, _=gettext
@@ -1053,7 +1062,7 @@ class ServerNode(PGChildNodeView):
 
             SQL = render_template("/".join([
                 'servers/sql',
-                '9.2_plus' if manager.version >= 90200 else '9.1_plus',
+                self.get_template_directory(manager.version),
                 'change_password.sql'
             ]),
                 conn=conn, _=gettext,
diff --git a/web/pgadmin/browser/server_groups/servers/templates/servers/sql/9.6_plus/change_password.sql b/web/pgadmin/browser/server_groups/servers/templates/servers/sql/9.6_plus/change_password.sql
new file mode 100644
index 0000000..dcf0ec1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/servers/sql/9.6_plus/change_password.sql
@@ -0,0 +1,2 @@
+{# Change database server password #}
+ALTER USER {{conn|qtIdent(user)}} WITH ENCRYPTED PASSWORD {{encrypted_password|qtLiteral}};
diff --git a/web/pgadmin/browser/server_groups/servers/templates/servers/sql/9.6_plus/stats.sql b/web/pgadmin/browser/server_groups/servers/templates/servers/sql/9.6_plus/stats.sql
new file mode 100644
index 0000000..a1dbc37
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/servers/sql/9.6_plus/stats.sql
@@ -0,0 +1,51 @@
+SELECT
+    pid AS "PID",
+    usename AS {{ conn|qtIdent(_('User')) }},
+    datname AS {{ conn|qtIdent(_('Database')) }},
+    backend_start AS {{ conn|qtIdent(_('Backend start')) }},
+    CASE
+    WHEN client_hostname IS NOT NULL AND client_hostname != '' THEN
+        client_hostname || ':' || client_port
+    WHEN client_addr IS NOT NULL AND client_addr::text != '' THEN
+        client_addr || ':' || client_port
+    WHEN client_port = -1 THEN
+        'local pipe'
+    ELSE
+        'localhost:' || client_port
+    END AS {{ conn|qtIdent(_('Client')) }},
+    application_name AS {{ conn|qtIdent(_('Application')) }},
+    wait_event_type AS {{ conn|qtIdent(_('Wait event type')) }},
+    wait_event AS {{ conn|qtIdent(_('Wait event name')) }},
+    query AS {{ conn|qtIdent(_('Query')) }},
+    query_start AS {{ conn|qtIdent(_('Query start')) }},
+    xact_start AS {{ conn|qtIdent(_('Xact start')) }}
+FROM
+    pg_stat_activity sa
+WHERE
+    (SELECT r.rolsuper OR r.oid = sa.usesysid  FROM pg_roles r WHERE r.rolname = current_user)
+UNION
+SELECT
+    pid AS "PID",
+    usename AS {{ conn|qtIdent(_('User')) }},
+    '' AS {{ conn|qtIdent(_('Database')) }},
+    backend_start AS {{ conn|qtIdent(_('Backend start')) }},
+    CASE
+    WHEN client_hostname IS NOT NULL AND client_hostname != '' THEN
+        client_hostname || ':' || client_port
+    WHEN client_addr IS NOT NULL AND client_addr::text != '' THEN
+        client_addr || ':' || client_port
+    WHEN client_port = -1 THEN
+        'local pipe'
+    ELSE
+        'localhost:' || client_port
+    END AS {{ conn|qtIdent(_('Client')) }},
+    {{ _('Streaming Replication')|qtLiteral }} AS {{ conn|qtIdent(_('Application')) }},
+    null AS {{ conn|qtIdent(_('Wait event type')) }},
+    null AS {{ conn|qtIdent(_('Wait event name')) }},
+    state || ' [sync (state: ' || COALESCE(sync_state, '') || ', priority: ' || sync_priority::text || ')] (' || sent_location || ' sent, ' || write_location || ' written, ' || flush_location || ' flushed, ' || replay_location || ' applied)' AS {{ conn|qtIdent(_('Query')) }},
+    null AS {{ conn|qtIdent(_('Query start')) }},
+    null AS {{ conn|qtIdent(_('Xact start')) }}
+FROM
+    pg_stat_replication sa
+WHERE
+    (SELECT r.rolsuper OR r.oid = sa.usesysid  FROM pg_roles r WHERE r.rolname = current_user)

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

---

From: Dave Page <[email-masked]>
Date: Thu, 22 Sep 2016 13:03:07 +0100

Can you rebase this please?

(pgadmin4)piranha:web dpage$ git apply ~/Downloads/RM_1719.patch
error: patch failed: web/pgadmin/browser/server_groups/servers/__init__.py:662
error: web/pgadmin/browser/server_groups/servers/__init__.py: patch
does not apply

On Thu, Sep 22, 2016 at 8:20 AM, Murtuza Zabuawala
<[email-masked]> wrote:
> Issue:

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Murtuza Zabuawala <[email-masked]>
Date: Thu, 22 Sep 2016 17:46:09 +0530

Hi Dave,

PFA updated patch.

diff --git a/web/pgadmin/browser/server_groups/servers/__init__.py b/web/pgadmin/browser/server_groups/servers/__init__.py
index 611d701..35494a2 100644
--- a/web/pgadmin/browser/server_groups/servers/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/__init__.py
@@ -656,6 +656,16 @@ class ServerNode(PGChildNodeView):
     def modified_sql(self, gid, sid):
         return make_json_response(data='')
 
@@ -665,7 +675,7 @@ class ServerNode(PGChildNodeView):
@@ -1040,7 +1050,7 @@ class ServerNode(PGChildNodeView):

---

From: Dave Page <[email-masked]>
Date: Thu, 22 Sep 2016 14:19:10 +0100

Thanks, applied.

On Thu, Sep 22, 2016 at 1:16 PM, Murtuza Zabuawala
<[email-masked]> wrote:
>> error: patch failed:
>> web/pgadmin/browser/server_groups/servers/__init__.py:662