def get_unused_indexes(self, name): db = sql_db.db_connect(name) cr = db.cursor() cr.execute( """SELECT relid::regclass as table, indexrelid::regclass as unused_index FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique IS FALSE AND pg_relation_size(relid::regclass) > 0;""" ) return cr.dictfetchall()
def get_missing_indexes(self, name): db = sql_db.db_connect(name) cr = db.cursor() cr.execute("""SELECT relname as table, seq_scan-idx_scan as too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END as index, pg_relation_size(relname::regclass) as table_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname='public' AND pg_relation_size(relname::regclass)>80000 ORDER BY too_much_seq DESC;""") return cr.dictfetchall()
def get_duplicated_indexes(self, name): db = sql_db.db_connect(name) cr = db.cursor() cr.execute("SELECT 0 FROM pg_proc WHERE proname = 'array_accum' AND proisagg;") if not cr.rowcount: cr.execute( "CREATE AGGREGATE array_accum (anyelement) (sfunc = array_append, stype = anyarray, initcond = '{}');" ) cr.execute( """SELECT indrelid::regclass as table, array_accum(indexrelid::regclass) as duplicated_indexes FROM pg_index GROUP BY indrelid, indkey HAVING count(*) > 1;""" ) return cr.dictfetchall()
def get_duplicated_indexes(self, name): db = sql_db.db_connect(name) cr = db.cursor() cr.execute( "SELECT 0 FROM pg_proc WHERE proname = 'array_accum' AND proisagg;") if not cr.rowcount: cr.execute( "CREATE AGGREGATE array_accum (anyelement) (sfunc = array_append, stype = anyarray, initcond = '{}');" ) cr.execute( """SELECT indrelid::regclass as table, array_accum(indexrelid::regclass) as duplicated_indexes FROM pg_index GROUP BY indrelid, indkey HAVING count(*) > 1;""") return cr.dictfetchall()
def get_missing_indexes(self, name): db = sql_db.db_connect(name) cr = db.cursor() cr.execute( """SELECT relname as table, seq_scan-idx_scan as too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END as index, pg_relation_size(relname::regclass) as table_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname='public' AND pg_relation_size(relname::regclass)>80000 ORDER BY too_much_seq DESC;""" ) return cr.dictfetchall()
def _create_empty_database(self, name): db = sql_db.db_connect('postgres') cr = db.cursor() chosen_template = tools.config['db_template'] cr.execute("""SELECT datname FROM pg_database WHERE datname = %s """, (name,)) if cr.fetchall(): raise openerp.exceptions.Warning(" %s database already exists!" % name ) try: cr.autocommit(True) # avoid transaction block cr.execute("""CREATE DATABASE "%s" ENCODING 'unicode' TEMPLATE "%s" """ % (name, chosen_template)) finally: cr.close()
def _create_empty_database(self, name): db = sql_db.db_connect('postgres') cr = db.cursor() chosen_template = tools.config['db_template'] cr.execute( """SELECT datname FROM pg_database WHERE datname = %s """, (name, )) if cr.fetchall(): raise openerp.exceptions.Warning(" %s database already exists!" % name) try: cr.autocommit(True) # avoid transaction block cr.execute( """CREATE DATABASE "%s" ENCODING 'unicode' TEMPLATE "%s" """ % (name, chosen_template)) finally: cr.close()