def __init__(self, name): self.name = name self.path = tempfile.mkdtemp() self.dbname = 'tablespace_db_%s' % name self.table_counter = 0 self.initial_data = None gparray = GpArray.initFromCatalog(dbconn.DbURL()) for host in gparray.getHostList(): run_cmd('ssh %s mkdir -p %s' % (pipes.quote(host), pipes.quote(self.path))) with dbconn.connect(dbconn.DbURL(), unsetSearchPath=False) as conn: db = pg.DB(conn) db.query("CREATE TABLESPACE %s LOCATION '%s'" % (self.name, self.path)) db.query("CREATE DATABASE %s TABLESPACE %s" % (self.dbname, self.name)) with dbconn.connect(dbconn.DbURL(dbname=self.dbname), unsetSearchPath=False) as conn: db = pg.DB(conn) db.query("CREATE TABLE tbl (i int) DISTRIBUTED RANDOMLY") db.query("INSERT INTO tbl VALUES (GENERATE_SERIES(0, 25))") # save the distributed data for later verification self.initial_data = db.query( "SELECT gp_segment_id, i FROM tbl").getresult()
def verify_for_gpexpand(self, hostname=None, port=0): """ For gpexpand, we need make sure: 1. data is the same after redistribution finished 2. the table's numsegments is enlarged to the new cluster size """ url = dbconn.DbURL(hostname=hostname, port=port, dbname=self.dbname) with dbconn.connect(url, unsetSearchPath=False) as conn: db = pg.DB(conn) data = db.query("SELECT gp_segment_id, i FROM tbl").getresult() tbl_numsegments = dbconn.execSQLForSingleton(conn, "SELECT numsegments FROM gp_distribution_policy " "WHERE localoid = 'tbl'::regclass::oid") num_segments = dbconn.execSQLForSingleton(conn, "SELECT COUNT(DISTINCT(content)) - 1 FROM gp_segment_configuration") if tbl_numsegments != num_segments: raise Exception("After gpexpand the numsegments for tablespace table 'tbl' %d does not match " "the number of segments in the cluster %d." % (tbl_numsegments, num_segments)) initial_data = [i for _, i in self.initial_data] data_without_segid = [i for _, i in data] if sorted(data_without_segid) != sorted(initial_data): raise Exception("Tablespace data is not identically distributed after running gp_expand. " "Expected pre-gpexpand data:\n%\n but found post-gpexpand data:\n%r" % ( sorted(self.initial_data), sorted(data)))
def performQuery(self, queryStatement="", operationType=1): ''' Put SQL statement into queryStatement. By default, operationType = 1, and it means that there is a return value. Set operationType = 0, if there is no return value. ''' rtnVal = None try: self.db_connection = pg.DB(dbname=self.db_name, host=self.db_ip, port=self.db_port, user=self.db_user, passwd=self.db_password) if operationType == 1: rtnVal = self.db_connection.query(queryStatement).dictresult() else: rtnVal = self.db_connection.query(queryStatement) except Exception as e: print( "*---------- Fail to connect to database or perform query statement. ----------*" ) print("------------Query statement is: %s" % queryStatement) print("------------Error message: %s" % str(e)) print( "*-----------------------------------------------------------------------------*" ) finally: if self.db_connection is not None: self.db_connection.close() return rtnVal
def drop_tables(): try: db = pg.DB(dbname='reuse_gptest' ,host='localhost' ,port=int(PGPORT) ) except Exception,e: errorMessage = str(e) print 'could not connect to database: ' + errorMessage
def trigger_fts_probe(self, port=0): self.logger.info('Triggering FTS probe') with dbconn.connect(dbconn.DbURL(port=port)) as conn: db = pg.DB(conn) # XXX Perform two probe scans in a row, to work around a known # race where gp_request_fts_probe_scan() can return early during the # first call. Remove this duplication once that race is fixed. for _ in range(2): db.query("SELECT gp_request_fts_probe_scan()")
def setUpClass(cls): # Connect to the database pointed to by PGHOST et al. with dbconn.connect(dbconn.DbURL()) as conn: # using the pg.DB connection so that each SQL is done as a single # transaction db = pg.DB(conn) test_database_name = "gpdb_test_database" db.query("DROP DATABASE IF EXISTS %s" % test_database_name) db.query("CREATE DATABASE %s" % test_database_name) cls.url = dbconn.DbURL(dbname=test_database_name)
def test_warnings_are_normally_suppressed(self): warning = "this is my warning message" with dbconn.connect(self.url) as conn: # Wrap our connection in pg.DB() so we can get at the underlying # notices. (This isn't available in the standard DB-API.) db = pg.DB(conn) self._raise_warning(db, warning) notices = db.notices() self.assertEqual(notices, []) # we expect no notices
def __init__(self, name): self.name = name self.path = tempfile.mkdtemp() self.dbname = 'tablespace_db_%s' % name self.table_counter = 0 self.initial_data = None with dbconn.connect(dbconn.DbURL()) as conn: db = pg.DB(conn) db.query("CREATE TABLESPACE %s LOCATION '%s'" % (self.name, self.path)) db.query("CREATE DATABASE %s TABLESPACE %s" % (self.dbname, self.name)) with dbconn.connect(dbconn.DbURL(dbname=self.dbname)) as conn: db = pg.DB(conn) db.query("CREATE TABLE tbl (i int) DISTRIBUTED RANDOMLY") db.query("INSERT INTO tbl VALUES (GENERATE_SERIES(0, 25))") # save the distributed data for later verification self.initial_data = db.query( "SELECT gp_segment_id, i FROM tbl").getresult()
def cleanup(self): with dbconn.connect(dbconn.DbURL(dbname="postgres")) as conn: db = pg.DB(conn) db.query("DROP DATABASE IF EXISTS %s" % self.dbname) db.query("DROP TABLESPACE IF EXISTS %s" % self.name) # Without synchronous_commit = 'remote_apply' introduced in 9.6, there # is no guarantee that the mirrors have removed their tablespace # directories by the time the DROP TABLESPACE command returns. # We need those directories to no longer be in use by the mirrors # before removing them below. _checkpoint_and_wait_for_replication_replay(db) shutil.rmtree(self.path)
def test_verbose_mode_allows_warnings_to_be_sent_to_the_client(self): warning = "this is my warning message" with dbconn.connect(self.url, verbose=True) as conn: db = pg.DB(conn) self._raise_warning(db, warning) notices = db.notices() for notice in notices: if warning in notice: return # found it! self.fail("Didn't find expected notice '{}' in {!r}".format( warning, notices))
def cleanup(self): with dbconn.connect(dbconn.DbURL(dbname="postgres"), unsetSearchPath=False) as conn: db = pg.DB(conn) db.query("DROP DATABASE IF EXISTS %s" % self.dbname) db.query("DROP TABLESPACE IF EXISTS %s" % self.name) # Without synchronous_commit = 'remote_apply' introduced in 9.6, there # is no guarantee that the mirrors have removed their tablespace # directories by the time the DROP TABLESPACE command returns. # We need those directories to no longer be in use by the mirrors # before removing them below. _checkpoint_and_wait_for_replication_replay(db) gparray = GpArray.initFromCatalog(dbconn.DbURL()) for host in gparray.getHostList(): run_cmd('ssh %s rm -rf %s' % (pipes.quote(host), pipes.quote(self.path)))
def verify(self, hostname=None, port=0): """ Verify tablespace functionality by ensuring the tablespace can be written to, read from, and the initial data is still correctly distributed. """ url = dbconn.DbURL(hostname=hostname, port=port, dbname=self.dbname) with dbconn.connect(url, unsetSearchPath=False) as conn: db = pg.DB(conn) data = db.query("SELECT gp_segment_id, i FROM tbl").getresult() # verify that we can still write to the tablespace self.table_counter += 1 db.query("CREATE TABLE tbl_%s (i int) DISTRIBUTED RANDOMLY" % self.table_counter) db.query("INSERT INTO tbl_%s VALUES (GENERATE_SERIES(0, 25))" % self.table_counter) if sorted(data) != sorted(self.initial_data): raise Exception("Tablespace data is not identically distributed. Expected:\n%r\n but found:\n%r" % ( sorted(self.initial_data), sorted(data)))
def pg_connect(start, rt_list, keeps): gpdb = pg.DB(host='mas01', dbname='csgbi', user='******', port=6666) end = time.time() runtime = end - start while runtime <= keeps: try: mystart = time.time() ran = random.randrange(1, 1000000000) query = """ insert into test values(%s,'test1','test2','test3') """ % (ran) gpdb.query(query) myend = time.time() rt = myend - mystart except Exception as e: print e rt = 0 end = time.time() runtime = end - start rt_list.append(rt) gpdb.close()
def __init__(self, dbname, host=None, port=None, user=None, passwd=None): args = {"dbname": dbname, "host": host, "user": user, "passwd": passwd} # pygresql comains if port is included in kwargs and its value is # None. It allows all other kwargs to be None. if port is not None: args["port"] = port self.db = pg.DB(**args) is_super = self.db.query("SHOW is_superuser").getresult() if is_super[0][0] != "on": user = self.db.query("SELECT CURRENT_USER").getresult() self.db.close() raise GPDBException("'%s' is not a superuser." % user[0][0]) # Ensure we only interact with a 4.3.x GPDB instance. version = self.db.query("SELECT version()").getresult() if re.match(".*Greenplum Database[ ]*4.3", version[0][0]) is None: self.db.close() raise GPDBException("GPDB is not running version 4.3.x") self.__systable_mods_set = False self.__catdml_created = False self.public_schema_is_created = False self.public_schema_oid = self.checkPublicSchemaOid()
def test_no_transaction_after_connect(self): with dbconn.connect(self.url) as conn: db = pg.DB(conn) # this would fail if we were in a transaction DROP DATABASE cannot # run inside a transaction block db.query("DROP DATABASE IF EXISTS some_nonexistent_database")
def test_connect(self): dburl = DbURL() logger.info("YO") db = pg.DB(dbname=dburl.pgdb) q = db.query("SELECT 1") logger.info(q.getresult())
def escape_string(string, conn): return pg.DB(db=conn).escape_string(string)
def connect(dbname, host, port, user, passwd): global db db = pg.DB(dbname=dbname, host=host, port=port, user=user, passwd=passwd)