listserial.py
author Radek Brich <brich.radek@ifortuna.cz>
Mon, 14 Apr 2014 22:28:12 +0200
changeset 97 a4af93e72e2b
parent 93 b72591087495
permissions -rwxr-xr-x
Add batchcopy tool.

#!/usr/bin/env python3

from pgtoolkit import toolbase, pgbrowser
from pycolib.ansicolor 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()