analyzeall.py
author Radek Brich <radek.brich@devl.cz>
Fri, 17 Aug 2012 11:07:22 +0200
changeset 40 922d7fb63384
parent 30 a8b7cd92f39f
child 52 26121a8fe78b
permissions -rwxr-xr-x
Add listserial tool. PgBrowser: Add list_sequences.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     2
"""
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     3
analyzeall
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     4
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     5
Analyze/vacuum all tables in selected schemas.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     6
See also "VACUUM ANALYZE VERBOSE;" query.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     7
Unlike that, this program skips pg_catalog etc.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     8
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     9
"""
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
from pgtoolkit import pgbrowser, toolbase
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
from pgtoolkit.highlight import highlight
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
class AnalyzeAllTool(toolbase.SimpleTool):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
    def __init__(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
        toolbase.SimpleTool.__init__(self, name='analyzeall', desc='Analyze all tables.')
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    18
        self.parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter')
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
        self.parser.add_argument('--vacuum', action='store_true', help='Call VACUUM ANALYZE')
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
        self.parser.add_argument('--full', action='store_true', help='Call VACUUM FULL ANALYZE')
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
    21
        self.target_isolation_level = 'autocommit'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
        self.init()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
    def main(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
        browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
        
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    27
        query_pattern = 'ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
        if self.args.vacuum:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    29
            query_pattern = 'VACUUM ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
        if self.args.full:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    31
            query_pattern = 'VACUUM FULL ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    33
        schema_list = self.args.schema
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    34
        if not schema_list:
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    35
            schema_list = [schema['name'] for schema in browser.list_schemas() if not schema['system']]
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    36
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    37
        for schema in schema_list:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
            tables = browser.list_tables(schema=schema)
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39
            with self.pgm.cursor('target') as curs:
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
                for table in tables:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    41
                    query = query_pattern % (schema, table['name'])
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    42
                    print(query)
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    43
                    curs.execute(query, [])
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    44
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    45
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    46
tool = AnalyzeAllTool()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
tool.main()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48