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 |
|