1 #!/usr/bin/env python3.2

     2 

     3 from pgtoolkit import toolbase, pgbrowser

     4 from pgtoolkit.highlight import highlight, WHITE, YELLOW, RED, BOLD

     5 

     6 

     7 class ListSerialTool(toolbase.SimpleTool):

     8     max_int = 2147483647

     9     

    10     def __init__(self):

    11         toolbase.SimpleTool.__init__(self, name='listserial', desc='List sequences attached to column of type integer with dangerous last_value.')

    12         self.init()

    13     

    14     def main(self):

    15         conn = self.pgm.get_conn('target')

    16         browser = pgbrowser.PgBrowser(conn)

    17         rows = browser.list_sequences()

    18         sequences = []

    19         for row in rows:

    20             if row['related_column_type'] == 'integer':

    21                 # read sequence attributes like last_value

    22                 q = 'SELECT * FROM "%s"."%s"' % (row['sequence_schema'], row['sequence_name'])

    23                 curs = conn.cursor()

    24                 curs.execute(q)

    25                 attrs = curs.fetchone_dict()

    26                 # skip this sequence if its cycled and has safe max_value

    27                 if attrs['is_cycled'] and attrs['max_value'] <= self.max_int:

    28                     continue

    29                 # skip sequences with last_value not yet in half of max_int

    30                 if attrs['last_value'] < self.max_int / 2:

    31                     continue

    32                 # remember rest of sequences

    33                 row['attrs'] = attrs

    34                 sequences.append(row)

    35         # sort most dangerous on top

    36         sequences.sort(key=lambda x: x['attrs']['last_value'], reverse=True)

    37         # print out what we've found

    38         for seq in sequences:

    39             print('Sequence:', seq['sequence_schema'] + '.' + seq['sequence_name'])

    40             print(' Related:', seq['sequence_schema'] + '.' + seq['related_table'], seq['related_column'], '(' + seq['related_column_type'] + ')')

    41             print('  integer max', '2147483647')

    42             # colorize last value

    43             last_val = seq['attrs']['last_value']

    44             col = WHITE + BOLD

    45             if last_val > self.max_int * 0.9:

    46                 # near max

    47                 col = YELLOW + BOLD

    48             if last_val > self.max_int:

    49                 # over max

    50                 col = RED + BOLD

    51             print('   last_value', highlight(1, col) + str(last_val) + highlight(0))

    52             for key in ('min_value', 'max_value', 'is_cycled'):

    53                 print('   ', key, seq['attrs'][key])

    54             print()

    55 

    56 

    57 tool = ListSerialTool()

    58 tool.main()

    59