pgtoolkit/pgstats.py
author Radek Brich <radek.brich@devl.cz>
Wed, 24 Jul 2013 13:11:37 +0200
changeset 84 3b5dd9efba35
parent 44 4fe39c59c515
permissions -rw-r--r--
Fix.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
2
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
# -*- coding: utf-8 -*-
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
#
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
# PgStats - browse database statistics
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
#
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
# Copyright (c) 2011  Radek Brich <radek.brich@devl.cz>
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
#
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
# Permission is hereby granted, free of charge, to any person obtaining a copy
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
# of this software and associated documentation files (the "Software"), to deal
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
# in the Software without restriction, including without limitation the rights
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
# copies of the Software, and to permit persons to whom the Software is
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
# furnished to do so, subject to the following conditions:
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
#
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
# The above copyright notice and this permission notice shall be included in
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
# all copies or substantial portions of the Software.
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
#
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
# THE SOFTWARE.
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
class PgStats:
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
    def __init__(self, conn=None):
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
        self.conn = conn
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
        
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
    def setconn(self, conn=None):
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
        self.conn = conn
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
    def _query(self, query, *args):
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
        try:
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
            curs = self.conn.cursor()
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36
            curs.execute(query, args)
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
            curs.connection.commit()
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
            rows = curs.fetchall()
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39
            return [dict(zip([desc[0] for desc in curs.description], row)) for row in rows]
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
        finally:
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
            curs.close()
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
    def list_long_queries(self, longer_than='1m'):
44
4fe39c59c515 Update longqueries tool: add client IP, waiting attributes.
Radek Brich <radek.brich@devl.cz>
parents: 39
diff changeset
    44
        return self._query('''SELECT
4fe39c59c515 Update longqueries tool: add client IP, waiting attributes.
Radek Brich <radek.brich@devl.cz>
parents: 39
diff changeset
    45
              datname, procpid, usename, current_query AS query,
4fe39c59c515 Update longqueries tool: add client IP, waiting attributes.
Radek Brich <radek.brich@devl.cz>
parents: 39
diff changeset
    46
              waiting, xact_start, query_start, backend_start, client_addr
2
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
            FROM pg_stat_activity
39
Radek Brich <radek.brich@devl.cz>
parents: 9
diff changeset
    48
            WHERE current_query <> '<IDLE>' AND query_start < now() - interval %s
Radek Brich <radek.brich@devl.cz>
parents: 9
diff changeset
    49
            ORDER BY query_start;''',
2
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
            longer_than)
efee419b7a2d Fix pgmanager exceptions.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    51