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_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()