1 from pgtoolkit.toolbase import SimpleTool

     2 from pgtoolkit import pgbrowser

     3 from pycolib.ansicolor import highlight, WHITE, YELLOW, RED, BOLD

     4 

     5 

     6 class ListSerialTool(SimpleTool):

     7 

     8     """List sequences near to overflow.

     9 

    10     Checks all sequences attached to a column of type integer.

    11 

    12     Highlight dangerous values of sequence:

    13     * Yellow - near overflow (90%)

    14     * Red - already over...

    15 

    16     Does not list sequences with value under 50% of range.

    17 

    18     """

    19 

    20     max_int = 2147483647

    21 

    22     def __init__(self):

    23         SimpleTool.__init__(self, name='listserial')

    24 

    25     def main(self):

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

    27         browser = pgbrowser.PgBrowser(conn)

    28         rows = browser.list_sequences()

    29         sequences = []

    30         for row in rows:

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

    32                 # read sequence attributes like last_value

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

    34                 curs = conn.cursor()

    35                 curs.execute(q)

    36                 attrs = curs.fetchone_dict()

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

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

    39                     continue

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

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

    42                     continue

    43                 # remember rest of sequences

    44                 row['attrs'] = attrs

    45                 sequences.append(row)

    46         # sort most dangerous on top

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

    48         # print out what we've found

    49         for seq in sequences:

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

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

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

    53             # colorize last value

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

    55             col = WHITE + BOLD

    56             if last_val > self.max_int * 0.9:

    57                 # near max

    58                 col = YELLOW + BOLD

    59             if last_val > self.max_int:

    60                 # over max

    61                 col = RED + BOLD

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

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

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

    65             print()

    66 

    67 

    68 cls = ListSerialTool

    69