--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pgtoolkit/tools/listserial.py	Tue May 06 18:37:43 2014 +0200
@@ -0,0 +1,69 @@
+from pgtoolkit.toolbase import SimpleTool
+from pgtoolkit import pgbrowser
+from pycolib.ansicolor import highlight, WHITE, YELLOW, RED, BOLD
+
+
+class ListSerialTool(SimpleTool):
+
+    """List sequences near to overflow.
+
+    Checks all sequences attached to a column of type integer.
+
+    Highlight dangerous values of sequence:
+    * Yellow - near overflow (90%)
+    * Red - already over...
+
+    Does not list sequences with value under 50% of range.
+
+    """
+
+    max_int = 2147483647
+
+    def __init__(self):
+        SimpleTool.__init__(self, name='listserial')
+
+    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()
+
+
+cls = ListSerialTool
+