listserial.py
author Radek Brich <radek.brich@devl.cz>
Tue, 05 Mar 2013 13:47:32 +0100
changeset 73 390376b9e70f
parent 40 922d7fb63384
child 83 515fadd3d286
permissions -rwxr-xr-x
Update longqueries tool: add --age parameter.

#!/usr/bin/env python3.2

from pgtoolkit import toolbase, pgbrowser
from pgtoolkit.highlight import highlight, WHITE, YELLOW, RED, BOLD


class ListSerialTool(toolbase.SimpleTool):
    max_int = 2147483647
    
    def __init__(self):
        toolbase.SimpleTool.__init__(self, name='listserial', desc='List sequences attached to column of type integer with dangerous last_value.')
        self.init()
    
    def main(self):
        conn = self.pgm.get_conn('target')
        browser = pgbrowser.PgBrowser(conn)
        rows = browser.list_sequences()
        sequences = []
        for row in rows:
            if row['related_column_type'] == 'integer':
                # read sequence attributes like last_value
                q = 'SELECT * FROM "%s"."%s"' % (row['sequence_schema'], row['sequence_name'])
                curs = conn.cursor()
                curs.execute(q)
                attrs = curs.fetchone_dict()
                # skip this sequence if its cycled and has safe max_value
                if attrs['is_cycled'] and attrs['max_value'] <= self.max_int:
                    continue
                # skip sequences with last_value not yet in half of max_int
                if attrs['last_value'] < self.max_int / 2:
                    continue
                # remember rest of sequences
                row['attrs'] = attrs
                sequences.append(row)
        # sort most dangerous on top
        sequences.sort(key=lambda x: x['attrs']['last_value'], reverse=True)
        # print out what we've found
        for seq in sequences:
            print('Sequence:', seq['sequence_schema'] + '.' + seq['sequence_name'])
            print(' Related:', seq['sequence_schema'] + '.' + seq['related_table'], seq['related_column'], '(' + seq['related_column_type'] + ')')
            print('  integer max', '2147483647')
            # colorize last value
            last_val = seq['attrs']['last_value']
            col = WHITE + BOLD
            if last_val > self.max_int * 0.9:
                # near max
                col = YELLOW + BOLD
            if last_val > self.max_int:
                # over max
                col = RED + BOLD
            print('   last_value', highlight(1, col) + str(last_val) + highlight(0))
            for key in ('min_value', 'max_value', 'is_cycled'):
                print('   ', key, seq['attrs'][key])
            print()


tool = ListSerialTool()
tool.main()