def discover_indexes(self,prod_cursor): self.cursor.execute("SELECT obj_oid,idx_name,id FROM index_name WHERE tn_id={0} AND alive".format(self.id)) local_idxs=self.cursor.fetchall() try: prod_cursor.execute("""SELECT i.indexrelid,c.relname,i.indisunique,i.indisprimary FROM pg_index i JOIN pg_class c ON i.indexrelid=c.oid WHERE i.indrelid={0}""".format(self.db_fields['obj_oid'])) except Exception as e: logger.error("Cannot execute index discovery query: {0},{1}".format(e.pgcode,e.pgerror)) return prod_idxs=prod_cursor.fetchall() for l_idx in local_idxs: for p_idx in prod_idxs: if l_idx[0]==p_idx[0] and l_idx[1]==p_idx[1]: break else: logger.info("Retired index {0} in table {1}".format(l_idx[1],self.db_fields['tbl_name'])) old_idx=IndexName(l_idx[2]) old_idx.retire() for p_idx in prod_idxs: for l_idx in local_idxs: if l_idx[0]==p_idx[0] and l_idx[1]==p_idx[1]: break else: logger.info("Create new index {0} in table {1}".format(p_idx[1],self.db_fields['tbl_name'])) new_index=IndexName() new_index.set_fields(tn_id=self.id,obj_oid=p_idx[0],idx_name=p_idx[1],is_unique=p_idx[2],is_primary=p_idx[3]) new_index.create() new_index.truncate()
def discover_indexes(self): p_cur=self._get_p_cursor() if not p_cur: logger.error("Returning from TN.discover_indexes No p_cur obtained") return False try: p_cur.execute("""SELECT i.indexrelid,c.relname,i.indisunique,i.indisprimary FROM pg_index i JOIN pg_class c ON i.indexrelid=c.oid WHERE i.indrelid={0}""".format(self.db_fields['obj_oid'])) except Exception as e: logger.error("Canot execute index discovery query on Prod: {0}".format(e.pgerror)) p_cur.close() return False prod_idxs=p_cur.fetchall() p_cur.close() cur=self._get_cursor() if not cur: logger.error("Returning from TN.discover_toast No cur obtained") return False try: cur.execute("SELECT obj_oid,idx_name,id FROM index_name WHERE tn_id={0} AND alive".format(self.id)) except Exception as e: logger.error("Canot execute index discovery query on Local: {0}".format(e.pgerror)) self.prod_conn.close() return False local_idxs=cur.fetchall() cur.close() for l_idx in local_idxs: for p_idx in prod_idxs: if l_idx[0]==p_idx[0] and l_idx[1]==p_idx[1]: break else: logger.info("Retired index {0} in table {1}".format(l_idx[1],self.db_fields['tbl_name'])) old_idx=IndexName(self.db_conn,self.prod_dsn,l_idx[2]) if not old_idx.retire(): logger.error("TN.discover_indexes Cannot retire old") for p_idx in prod_idxs: for l_idx in local_idxs: if l_idx[0]==p_idx[0] and l_idx[1]==p_idx[1]: break else: new_index=IndexName(self.db_conn,self.prod_dsn) new_index.set_fields(tn_id=self.id,obj_oid=p_idx[0],idx_name=p_idx[1],is_unique=p_idx[2],is_primary=p_idx[3]) if not new_index._create(): logger.error("TN.discover_indexes Cannot create new") logger.info("Create new index {0} in table {1}".format(p_idx[1],self.db_fields['tbl_name'])) return True