Python PSQL.run_sql_command_utility_mode方法代码示例

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_21_use_udf_gp_aovisimap_hidden_info_uao_upd_vacuum(self):
        tablename ='uao_table_test11'
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('test_21 Verify the hidden tup_count using UDF gp_aovisimap_hidden_info(oid)  for uao relation after update_vacuum')
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath,'create_tab_tupcount_in_pg_aoseg_uaotable_upd_11.out')
        sql_file = os.path.join(self.sqlpath,'create_tab_tupcount_in_pg_aoseg_uaotable_upd_11.sql')
        ans_file= os.path.join(self.anspath,'create_tab_tupcount_in_pg_aoseg_uaotable_upd_11.ans')

        #create uao table and insert 10 rows
        sql_out=PSQL.run_sql_file(sql_file = sql_file,out_file=out_file)
        assert Gpdiff.are_files_equal(out_file, ans_file)
        #get relid for newly created table
        relid = self.get_relid(file_name=tablename )

        #get relid for newly created table
        relid = self.get_relid(file_name=tablename )
        #get utility mode connection info
        utilitymodeinfo=self.get_utilitymode_conn_info( relid=relid)
        u_port=utilitymodeinfo[0]
        u_host=utilitymodeinfo[1]

        assert(0 == int(self.get_hidden_tup_cnt(relid=relid,host=u_host,port= u_port)))

        # update  rows
        sql_cmd3="update %s set j = 'test11' ;" % ( tablename)
        PSQL.run_sql_command_utility_mode(sql_cmd= sql_cmd3,host=u_host, port=u_port,flags='-q -t')

        assert(int(self.get_hidden_tup_cnt(relid=relid,host=u_host,port= u_port)) > 0)

        self.vacuum_full(tablename=tablename)
        assert(0 == int(self.get_hidden_tup_cnt(relid=relid,host=u_host,port= u_port)))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_18_gp_persistent_relation_node_uaocs_table_eof_upd(self):
        tablename ='uaocs_table_test14'
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('test_18 Verify the eof mark in pg_aoseg and gp_persistant_rel_node table for uaocs relation after update ')
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath,'create_tab_gp_persistent_relation_node_uaocs_table_upd_14.out')
        sql_file = os.path.join(self.sqlpath,'create_tab_gp_persistent_relation_node_uaocs_table_upd_14.sql')
        ans_file= os.path.join(self.anspath,'create_tab_gp_persistent_relation_node_uaocs_table_upd_14.ans')
	#create uaocs table and insert 10 rows
        sql_out=PSQL.run_sql_file(sql_file = sql_file,out_file=out_file)
        assert Gpdiff.are_files_equal(out_file, ans_file)
        #get relid for newly created table
        relid = self.get_relid(file_name=tablename )
	#get utility mode connection info
        utilitymodeinfo=self.get_utilitymode_conn_uaocs_info( relid=relid)
        u_port=utilitymodeinfo[0]
        u_host=utilitymodeinfo[1]

        assert (self.is_same_eof_uaocs_on_segment(relid=relid,host=u_host,port= u_port))

	# delete 1 row
        sql_cmd3="delete from  %s  where i = (select min(i) from %s );" % (tablename, tablename)
        PSQL.run_sql_command_utility_mode(sql_cmd= sql_cmd3,host=u_host, port=u_port,flags='-q -t')
        self.vacuum_full(tablename=tablename)

        assert (self.is_same_eof_uaocs_on_segment(relid=relid,host=u_host,port= u_port))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_20_use_udf_gp_aovisimap_hidden_info_uaocs_del(self):
        tablename ='uaocs_table_test14'
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('test_20 Verify the hidden tup_count using UDF gp_aovisimap_hidden_info(oid)  for uaocs relation after delete ')
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath,'create_tab_gp_persistent_relation_node_uaocs_table_upd_14.out')
        sql_file = os.path.join(self.sqlpath,'create_tab_gp_persistent_relation_node_uaocs_table_upd_14.sql')
        ans_file= os.path.join(self.anspath,'create_tab_gp_persistent_relation_node_uaocs_table_upd_14.ans')
        #create uaocs table and insert 10 rows
        sql_out=PSQL.run_sql_file(sql_file = sql_file,out_file=out_file)
        assert Gpdiff.are_files_equal(out_file, ans_file)
        #get relid for newly created table
        relid = self.get_relid(file_name=tablename )
        #get utility mode connection info
        utilitymodeinfo=self.get_utilitymode_conn_uaocs_info( relid=relid)
        u_port=utilitymodeinfo[0]
        u_host=utilitymodeinfo[1]

        assert(0 == int(self.get_hidden_tup_cnt(relid=relid,host=u_host,port= u_port)))

        # delete 1 row
        sql_cmd3="delete from  %s  where i = (select min(i) from %s );" % (tablename, tablename)
        PSQL.run_sql_command_utility_mode(sql_cmd= sql_cmd3,host=u_host, port=u_port,flags='-q -t')

        assert(1 == int(self.get_hidden_tup_cnt(relid=relid,host=u_host,port= u_port)))

        self.vacuum_full(tablename=tablename)
        assert(0 == int(self.get_hidden_tup_cnt(relid=relid,host=u_host,port= u_port)))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_17_gp_persistent_relation_node_uaocs_table_eof_del(self):
        tablename = "uaocs_table_test14"
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info(
            "test_17 Verify the eof mark in pg_aoseg and gp_persistant_rel_node table for uaocs relation after delete "
        )
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath, "create_tab_gp_persistent_relation_node_uaocs_table_upd_14.out")
        sql_file = os.path.join(self.sqlpath, "create_tab_gp_persistent_relation_node_uaocs_table_upd_14.sql")
        ans_file = os.path.join(self.anspath, "create_tab_gp_persistent_relation_node_uaocs_table_upd_14.ans")
        # create uaocs table and insert 10 rows
        sql_out = PSQL.run_sql_file(sql_file=sql_file, out_file=out_file)
        assert Gpdiff.are_files_equal(out_file, ans_file)
        # get relid for newly created table
        relid = self.get_relid(file_name=tablename)
        # get utility mode connection info
        utilitymodeinfo = self.get_utilitymode_conn_uaocs_info(relid=relid)
        u_port = utilitymodeinfo[0]
        u_host = utilitymodeinfo[1]
        assert self.is_same_eof_uaocs_on_segment(relid=relid, host=u_host, port=u_port)

        # update 1 row
        sql_cmd3 = "update %s set k = k+ 100 where i = (select min(i) from %s );" % (tablename, tablename)
        PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd3, host=u_host, port=u_port, flags="-q -t")
        self.vacuum()
        # self.vacuum(tablename=tablename)

        assert self.is_same_eof_uaocs_on_segment(relid=relid, host=u_host, port=u_port)

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_13_gp_persistent_relation_node_uaotable_del(self):
        tablename = "uao_table_test13"
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info(
            "test_13 Verify the eof mark in pg_aoseg and gp_persistant_rel_node table for uao relation after delete "
        )
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath, "create_tab_gp_persistent_relation_node_uaotable_del_13.out")
        sql_file = os.path.join(self.sqlpath, "create_tab_gp_persistent_relation_node_uaotable_del_13.sql")
        ans_file = os.path.join(self.anspath, "create_tab_gp_persistent_relation_node_uaotable_del_13.ans")

        # create uao table and insert 10 rows
        sql_out = PSQL.run_sql_file(sql_file=sql_file, out_file=out_file)
        assert Gpdiff.are_files_equal(out_file, ans_file)
        # get relid for newly created table
        relid = self.get_relid(file_name=tablename)
        # get utility mode connection info
        utilitymodeinfo = self.get_utilitymode_conn_info(relid=relid)
        u_port = utilitymodeinfo[0]
        u_host = utilitymodeinfo[1]

        # get eof info before delete
        assert self.is_same_eof_uao_on_segment(relid=relid, host=u_host, port=u_port)

        # delete 1 row
        sql_cmd3 = "delete from %s  where i = (select min(i) from %s );" % (tablename, tablename)
        PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd3, host=u_host, port=u_port, flags="-q -t")
        self.vacuum_full(tablename=tablename)

        # get eof info after delete
        # eof_pg_aoseg_aft=self.get_eofuncompressed_pgaoseg_on_segment(relid=relid,host=u_host,port= u_port)
        # eof_gp_persistant_rel_node_aft=self.get_eof_gp_persistent_relation_node_on_segment(relid=relid,host=u_host,port= u_port)
        # assert (int(eof_pg_aoseg_aft) == (int(eof_gp_persistant_rel_node_aft)))
        assert self.is_same_eof_uao_on_segment(relid=relid, host=u_host, port=u_port)

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_10_use_udf_gp_aovisimap_hidden_info_uao_del_vacuum(self):
        tablename = "uao_table_test11"
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info(
            "test_10 Verify the hidden tup_count using udf gp_aovisimap_hidden_info(oid)  for uao relation after delete and vacuum"
        )
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath, "create_tab_tupcount_in_pg_aoseg_uaotable_upd_11.out")
        sql_file = os.path.join(self.sqlpath, "create_tab_tupcount_in_pg_aoseg_uaotable_upd_11.sql")
        ans_file = os.path.join(self.anspath, "create_tab_tupcount_in_pg_aoseg_uaotable_upd_11.ans")

        # create uao table and insert 10 rows
        sql_out = PSQL.run_sql_file(sql_file=sql_file, out_file=out_file)
        assert Gpdiff.are_files_equal(out_file, ans_file)
        # get relid for newly created table
        relid = self.get_relid(file_name=tablename)

        # get relid for newly created table
        relid = self.get_relid(file_name=tablename)
        # get utility mode connection info
        utilitymodeinfo = self.get_utilitymode_conn_info(relid=relid)
        u_port = utilitymodeinfo[0]
        u_host = utilitymodeinfo[1]

        assert 0 == int(self.get_hidden_tup_cnt(relid=relid, host=u_host, port=u_port))

        # delete  row
        sql_cmd3 = "delete from  %s  ;" % (tablename)
        PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd3, host=u_host, port=u_port, flags="-q -t")
        assert int(self.get_hidden_tup_cnt(relid=relid, host=u_host, port=u_port)) > 0

        self.vacuum_full(tablename=tablename)
        assert 0 == int(self.get_hidden_tup_cnt(relid=relid, host=u_host, port=u_port))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_09_call_udf_gp_aovisimap_forupdate(self):
	tablename='uao_visimap_test09'
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('test_09 Verify the usage of UDF gp_aovisimap in utility mode for update tuple')
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath,'create_tab_gp_aovisimap_upd_09.out')
        sql_file = os.path.join(self.sqlpath,'create_tab_gp_aovisimap_upd_09.sql')
        ans_file= os.path.join(self.anspath,'create_tab_gp_aovisimap_upd_09.ans')

        #create uao table and insert 10 rows
        sql_out=PSQL.run_sql_file(sql_file = sql_file,out_file=out_file)
        assert Gpdiff.are_files_equal(out_file, ans_file)
        #get relid for newly created table
        relid = self.get_relid(file_name=tablename)
	utilitymodeinfo=self.get_utilitymode_conn_info( relid=relid)
        u_port=utilitymodeinfo[0]
        u_host=utilitymodeinfo[1]
        #login to segment in utility mode and execute the gp_aovisimap(relid) UDF
        before_tablerowcnt=self.get_rowcnt_table_on_segment(tablename=tablename, host=u_host,port=u_port)
        before_visimaprowcnt=self.get_visimap_cnt_on_segment(relid=relid,host=u_host,port=u_port)
        assert(int(before_visimaprowcnt) == 0)
        sql_cmd="update %s set j = j || '_9';" % (tablename)
        PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd,host=u_host, port=u_port,flags='-q -t')
        after_visimaprowcnt=self.get_visimap_cnt_on_segment(relid=relid,host=u_host,port=u_port)
        assert(int(after_visimaprowcnt) > 0)

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def test_08_call_udf_gp_aovisimap_fordelete(self):
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('test_08 Verify the usage of UDF gp_aovisimap in utility mode for deleted tuple')
        tinctest.logger.info("-------------------------------\n")
        out_file = os.path.join(self.outpath,'create_tab_gp_aovisimap_del_08.out')
        sql_file = os.path.join(self.sqlpath,'create_tab_gp_aovisimap_del_08.sql')
        ans_file= os.path.join(self.anspath,'create_tab_gp_aovisimap_del_08.ans')
	tablename='uao_visimap_test08'

	#create uao table and insert 10 rows
        sql_out=PSQL.run_sql_file(sql_file = sql_file,out_file=out_file)
	assert Gpdiff.are_files_equal(out_file, ans_file)
	#get relid for newly created table
        relid = self.get_relid(file_name=tablename )
	#get the segment_id where we'll log in utility mode and then get the hostname and port for this segment
        utilitymodeinfo=self.get_utilitymode_conn_info( relid=relid)
        u_port=utilitymodeinfo[0]
        u_host=utilitymodeinfo[1]

	before_tablerowcnt=self.get_rowcnt_table_on_segment(tablename=tablename, host=u_host,port=u_port)
	before_visimaprowcnt=self.get_visimap_cnt_on_segment(relid=relid,host=u_host,port=u_port)
	assert(int(before_visimaprowcnt) == 0)
	sql_cmd="delete from uao_visimap_test08 ;"
        PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd,host=u_host, port=u_port,flags='-q -t')	
	after_tablerowcnt=self.get_rowcnt_table_on_segment(tablename=tablename, host=u_host,port=u_port)
	after_visimaprowcnt=self.get_visimap_cnt_on_segment(relid=relid,host=u_host,port=u_port)
	assert(int(after_tablerowcnt) == 0)

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
 def switch_ckpt_switch_xlog(self):
     '''
     @description: pg_switch_xlog on segments
     '''
     sql_cmd = 'select * from pg_switch_xlog();'
     num_primary = self.config.get_countprimarysegments()
     for i in range(num_primary):
         (host, port) = self.config.get_hostandport_of_segment(psegmentNumber=i)
         PSQL.run_sql_command_utility_mode(sql_cmd, host = host, port = port)

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
 def test_run_sql_command_utility_mode(self):
     sql_cmd = 'show gp_session_role'
     out_file = os.path.join(os.path.dirname(inspect.getfile(self.__class__)),'test_utility_mode.out')
     self.assertFalse(os.path.exists(out_file))
     try:
         PSQL.run_sql_command_utility_mode(sql_cmd = sql_cmd, out_file = out_file)
         self.assertTrue(os.path.exists(out_file))
         with open(out_file, 'r') as f:
             output = f.read()
             self.assertIsNotNone(re.search('utility', output))
     finally:
         os.remove(out_file)
         self.assertFalse(os.path.exists(out_file))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def get_eofuncompressed_pgaoseg_on_segment(self,relid=0,host=None,port=None):
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('getting eofuncompressed from pg_aoseg.pg_aoseg_ '+relid)
        tinctest.logger.info("-------------------------------\n")
	sql_cmd="select eofuncompressed  from pg_aoseg.pg_aoseg_%s where eof > 0 LIMIT 1;" % relid.strip()
	eof=PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd,host=host, port=port,flags='-q -t')
	return eof

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def get_rowcnt_table_on_segment(self,tablename=None, host=None,port=None):
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('getting rowcount from table '+tablename +' on segment host='+host +' port=' + port)
        tinctest.logger.info("-------------------------------\n")
	sql_cmd="select count(*) from %s;" % tablename
        rowcount= PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd,host=host, port=port,flags='-q -t')
        return rowcount

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
 def get_visimap_cnt_on_segment(self,relid=0,host=None,port=None):
     tinctest.logger.info("-------------------------------")
     tinctest.logger.info('getting rowcount from relid '+ relid +' on segment host='+host +' port=' + port)
     tinctest.logger.info("-------------------------------\n")
     sql_cmd="select count(*) from gp_aovisimap(%s);" % relid.strip()
     rowcount= PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd,host=host, port=port,flags='-q -t')
     return rowcount

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def get_hidden_tup_cnt(self, relid=0,host=None,port=None):
	tinctest.logger.info("-------------------------------")
        tinctest.logger.info('get hidden_tupcount from gp_aovisimap_hidden_info ')
        tinctest.logger.info("-------------------------------\n")
	sql_cmd="select sum(hidden_tupcount) from gp_aovisimap_hidden_info(%s);" % relid.strip()
	tupcnt=PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd,host=host, port=port,flags='-q -t')
	if (len(tupcnt.strip()) == 0):
		tupcnt='0'
	return tupcnt

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_command_utility_mode [as 别名]
    def is_same_eof_uao_on_segment(self,relid=0,host=None,port=None):
        tinctest.logger.info("-------------------------------")
        tinctest.logger.info('getting eofuncompressed from gp_persistent_relation_node and pg_aoseg.pg_aoseg_oid ')
        tinctest.logger.info("-------------------------------\n")
	sql_cmd="select count(*) from gp_persistent_relation_node a , pg_aoseg.pg_aoseg_%s b where a.segment_file_num = b.segno and relfilenode_oid = %s and mirror_append_only_new_eof != eofuncompressed;" %(relid.strip(),relid.strip())
	cnt=PSQL.run_sql_command_utility_mode(sql_cmd=sql_cmd,host=host, port=port,flags='-q -t')
	if (int(cnt) == 0):
		return True
	else:
		return False