def get_pg_conn(db_host, db, db_user, db_pwd, schema_name, db_port=5439, query_group=None, query_slot_count=1, ssl=True, **kwargs): conn = None if debug: comment('Connect %s:%s:%s:%s' % (db_host, db_port, db, db_user)) try: conn = pg8000.connect(user=db_user, host=db_host, port=int(db_port), database=db, password=db_pwd, ssl=ssl, timeout=None) conn._usock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1) conn.autocommit = True except Exception as e: print("Exception on Connect to Cluster: %s" % e) print('Unable to connect to Cluster Endpoint') cleanup(conn) return None # set search paths aws_utils.set_search_paths(conn, schema_name) if query_group is not None and query_group != '': set_query_group = 'set query_group to %s' % query_group if debug: comment(set_query_group) run_commands(conn, [set_query_group]) set_slot_count = None if query_slot_count is not None and query_slot_count > 1: set_slot_count = 'set wlm_query_slot_count = %s' % query_slot_count if set_slot_count is not None: if debug: comment(set_slot_count) run_commands(conn, [set_slot_count]) # set a long statement timeout set_timeout = "set statement_timeout = '36000000'" if debug: comment(set_timeout) run_commands(conn, [set_timeout]) # set application name set_name = "set application_name to 'AnalyzeVacuumUtility-v%s'" % __version__ if debug: comment(set_name) run_commands(conn, [set_name]) comment("Connected to %s:%s:%s as %s" % (db_host, db_port, db, db_user)) return conn
def get_pg_conn(db_host, db, db_user, db_pwd, schema_name, db_port=5439, query_group=None, query_slot_count=1, ssl=True, **kwargs): conn = None if debug: comment('Connect %s:%s:%s:%s' % (db_host, db_port, db, db_user)) try: conn = pg8000.connect(user=db_user, host=db_host, port=int(db_port), database=db, password=db_pwd, ssl=ssl, timeout=None) conn._usock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1) conn.autocommit = True except Exception as e: print("Exception on Connect to Cluster: %s" % e) print('Unable to connect to Cluster Endpoint') cleanup(conn) return None # set search paths aws_utils.set_search_paths(conn, schema_name, exclude_external_schemas=True) if query_group is not None and query_group != '': set_query_group = 'set query_group to %s' % query_group if debug: comment(set_query_group) run_commands(conn, [set_query_group]) set_slot_count = None if query_slot_count is not None and query_slot_count > 1: set_slot_count = 'set wlm_query_slot_count = %s' % query_slot_count if set_slot_count is not None: if debug: comment(set_slot_count) run_commands(conn, [set_slot_count]) # set a long statement timeout set_timeout = "set statement_timeout = '36000000'" if debug: comment(set_timeout) run_commands(conn, [set_timeout]) # set application name set_name = "set application_name to 'AnalyzeVacuumUtility-v%s'" % __version__ if debug: comment(set_name) run_commands(conn, [set_name]) comment("Connected to %s:%s:%s as %s" % (db_host, db_port, db, db_user)) return conn
def get_pg_conn(): global db_connections pid = str(os.getpid()) conn = None # get the database connection for this PID try: conn = db_connections[pid] except KeyError: pass if conn is None: # connect to the database if debug: comment('Connect [%s] %s:%s:%s:%s' % (pid, db_host, db_port, db, db_user)) try: conn = pg8000.connect(user=db_user, host=db_host, port=db_port, database=db, password=db_pwd, ssl=ssl, timeout=None) # Enable keepalives manually untill pg8000 supports it # For future reference: https://github.com/mfenniak/pg8000/issues/149 # TCP keepalives still need to be configured appropriately on OS level as well conn._usock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1) conn.autocommit = True except Exception as e: print(e) print('Unable to connect to Cluster Endpoint') cleanup(conn) return ERROR aws_utils.set_search_paths(conn, schema_name, target_schema) if query_group is not None: set_query_group = 'set query_group to %s' % query_group if debug: comment(set_query_group) run_commands(conn, [set_query_group]) if query_slot_count is not None and query_slot_count != 1: set_slot_count = 'set wlm_query_slot_count = %s' % query_slot_count if debug: comment(set_slot_count) run_commands(conn, [set_slot_count]) # set a long statement timeout set_timeout = "set statement_timeout = '1200000'" if debug: comment(set_timeout) run_commands(conn, [set_timeout]) # set application name set_name = "set application_name to 'ColumnEncodingUtility-v%s'" % __version__ if debug: comment(set_name) run_commands(conn, [set_name]) # turn off autocommit for the rest of the executions conn.autocommit = False # cache the connection db_connections[pid] = conn return conn
def get_pg_conn(): global db_connections pid = str(os.getpid()) conn = None # get the database connection for this PID try: conn = db_connections[pid] except KeyError: pass if conn is None: # connect to the database if debug: comment('Connect [%s] %s:%s:%s:%s' % (pid, db_host, db_port, db, db_user)) try: conn = pg8000.connect(user=db_user, host=db_host, port=db_port, database=db, password=db_pwd, ssl=ssl, timeout=None) # Enable keepalives manually untill pg8000 supports it # For future reference: https://github.com/mfenniak/pg8000/issues/149 # TCP keepalives still need to be configured appropriately on OS level as well conn._usock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1) conn.autocommit = True except Exception as e: print(e) print('Unable to connect to Cluster Endpoint') cleanup(conn) return ERROR aws_utils.set_search_paths(conn, schema_name, target_schema, exclude_external_schemas=True) if query_group is not None: set_query_group = 'set query_group to %s' % query_group if debug: comment(set_query_group) run_commands(conn, [set_query_group]) if query_slot_count is not None and query_slot_count != 1: set_slot_count = 'set wlm_query_slot_count = %s' % query_slot_count if debug: comment(set_slot_count) run_commands(conn, [set_slot_count]) # set a long statement timeout set_timeout = "set statement_timeout = '%s'" % statement_timeout if debug: comment(set_timeout) run_commands(conn, [set_timeout]) # set application name set_name = "set application_name to 'ColumnEncodingUtility-v%s'" % __version__ if debug: comment(set_name) run_commands(conn, [set_name]) # turn off autocommit for the rest of the executions conn.autocommit = False # cache the connection db_connections[pid] = conn return conn