def get_info(self): """Get tablespace information.""" # Check that spcoptions exists: opt = exec_sql(self, "SELECT 1 FROM information_schema.columns " "WHERE table_name = 'pg_tablespace' " "AND column_name = 'spcoptions'", add_to_executed=False) # For 9.1 version and earlier: location = exec_sql(self, "SELECT 1 FROM information_schema.columns " "WHERE table_name = 'pg_tablespace' " "AND column_name = 'spclocation'", add_to_executed=False) if location: location = 'spclocation' else: location = 'pg_tablespace_location(t.oid)' if not opt: self.opt_not_supported = True query = ("SELECT r.rolname, (SELECT Null), %s " "FROM pg_catalog.pg_tablespace AS t " "JOIN pg_catalog.pg_roles AS r " "ON t.spcowner = r.oid " % location) else: query = ("SELECT r.rolname, t.spcoptions, %s " "FROM pg_catalog.pg_tablespace AS t " "JOIN pg_catalog.pg_roles AS r " "ON t.spcowner = r.oid " % location) res = exec_sql(self, query + "WHERE t.spcname = %(name)s", query_params={'name': self.name}, add_to_executed=False) if not res: self.exists = False return False if res[0][0]: self.exists = True self.owner = res[0][0] if res[0][1]: # Options exist: for i in res[0][1]: i = i.split('=') self.settings[i[0]] = i[1] if res[0][2]: # Location exists: self.location = res[0][2]
def copy_to(self): """Implements COPY TO command behavior.""" self.src = self.module.params['src'] self.dst = self.module.params['copy_to'] if 'SELECT ' in self.src.upper(): # If src is SQL SELECT statement: query_fragments = ['COPY (%s)' % self.src] else: # If src is a table: query_fragments = ['COPY %s' % pg_quote_identifier(self.src, 'table')] if self.module.params.get('columns'): query_fragments.append('(%s)' % ','.join(self.module.params['columns'])) query_fragments.append('TO') if self.module.params.get('program'): query_fragments.append('PROGRAM') query_fragments.append("'%s'" % self.dst) if self.module.params.get('options'): query_fragments.append(self.__transform_options()) # Note: check mode is implemented here: if self.module.check_mode: self.changed = self.__check_table(self.src) if self.changed: self.executed_queries.append(' '.join(query_fragments)) else: if exec_sql(self, ' '.join(query_fragments), ddl=True): self.changed = True
def create(self): """Implements CREATE SEQUENCE command behavior.""" query = ['CREATE SEQUENCE'] query.append(self.__add_schema()) if self.module.params.get('data_type'): query.append('AS %s' % self.module.params['data_type']) if self.module.params.get('increment'): query.append('INCREMENT BY %s' % self.module.params['increment']) if self.module.params.get('minvalue'): query.append('MINVALUE %s' % self.module.params['minvalue']) if self.module.params.get('maxvalue'): query.append('MAXVALUE %s' % self.module.params['maxvalue']) if self.module.params.get('start'): query.append('START WITH %s' % self.module.params['start']) if self.module.params.get('cache'): query.append('CACHE %s' % self.module.params['cache']) if self.module.params.get('cycle'): query.append('CYCLE') return exec_sql(self, ' '.join(query), ddl=True)
def drop(self, schema, cascade=False, concurrent=True): """Drop PostgreSQL index. Return True if success, otherwise, return False. Args: schema (str) -- name of the index schema Kwargs: cascade (bool) -- automatically drop objects that depend on the index, default False concurrent (bool) -- build index in concurrent mode, default True """ if not self.exists: return False query = 'DROP INDEX' if concurrent: query += ' CONCURRENTLY' if not schema: query += ' public.%s' % self.name else: query += ' %s.%s' % (schema, self.name) if cascade: query += ' CASCADE' self.executed_query = query if exec_sql(self, query, ddl=True, add_to_executed=False): return True return False
def __exists_in_db(self): """Check table exists and refresh info""" if "." in self.name: schema = self.name.split('.')[-2] tblname = self.name.split('.')[-1] else: schema = 'public' tblname = self.name query = ("SELECT t.tableowner, t.tablespace, c.reloptions " "FROM pg_tables AS t " "INNER JOIN pg_class AS c ON c.relname = t.tablename " "INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid " "WHERE t.tablename = %(tblname)s " "AND n.nspname = %(schema)s") res = exec_sql(self, query, query_params={ 'tblname': tblname, 'schema': schema }, add_to_executed=False) if res: self.exists = True self.info = dict( owner=res[0][0], tblspace=res[0][1] if res[0][1] else '', storage_params=res[0][2] if res[0][2] else [], ) return True else: self.exists = False return False
def reassign(self, old_owners, fail_on_role): """Implements REASSIGN OWNED BY command. If success, set self.changed as True. Arguments: old_owners (list): The ownership of all the objects within the current database, and of all shared objects (databases, tablespaces), owned by these roles will be reassigned to self.role. fail_on_role (bool): If True, fail when a role from old_owners does not exist. Otherwise just warn and continue. """ roles = [] for r in old_owners: if self.check_role_exists(r, fail_on_role): roles.append(pg_quote_identifier(r, 'role')) # Roles do not exist, nothing to do, exit: if not roles: return False old_owners = ','.join(roles) query = ['REASSIGN OWNED BY'] query.append(old_owners) query.append('TO %s' % pg_quote_identifier(self.role, 'role')) query = ' '.join(query) self.changed = exec_sql(self, query, ddl=True)
def __exists_in_db(self): """Check index existence, collect info, add it to self.info dict. Return True if the index exists, otherwise, return False. """ query = ("SELECT i.schemaname, i.tablename, i.tablespace, " "pi.indisvalid, c.reloptions " "FROM pg_catalog.pg_indexes AS i " "JOIN pg_catalog.pg_class AS c " "ON i.indexname = c.relname " "JOIN pg_catalog.pg_index AS pi " "ON c.oid = pi.indexrelid " "WHERE i.indexname = %(name)s") res = exec_sql(self, query, query_params={'name': self.name}, add_to_executed=False) if res: self.exists = True self.info = dict( name=self.name, state='present', schema=res[0][0], tblname=res[0][1], tblspace=res[0][2] if res[0][2] else '', valid=res[0][3], storage_params=res[0][4] if res[0][4] else [], ) return True else: self.exists = False return False
def copy_from(self): """Implements COPY FROM command behavior.""" self.src = self.module.params['copy_from'] self.dst = self.module.params['dst'] query_fragments = ['COPY %s' % pg_quote_identifier(self.dst, 'table')] if self.module.params.get('columns'): query_fragments.append('(%s)' % ','.join(self.module.params['columns'])) query_fragments.append('FROM') if self.module.params.get('program'): query_fragments.append('PROGRAM') query_fragments.append("'%s'" % self.src) if self.module.params.get('options'): query_fragments.append(self.__transform_options()) # Note: check mode is implemented here: if self.module.check_mode: self.changed = self.__check_table(self.dst) if self.changed: self.executed_queries.append(' '.join(query_fragments)) else: if exec_sql(self, ' '.join(query_fragments), ddl=True): self.changed = True
def set_schema(self): """Implements ALTER SEQUENCE SET SCHEMA command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append('SET SCHEMA %s' % pg_quote_identifier(self.module.params['newschema'], 'schema')) return exec_sql(self, ' '.join(query), ddl=True)
def set_owner(self): """Implements ALTER SEQUENCE OWNER TO command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append('OWNER TO %s' % pg_quote_identifier(self.module.params['owner'], 'role')) return exec_sql(self, ' '.join(query), ddl=True)
def rename(self): """Implements ALTER SEQUENCE RENAME TO command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append('RENAME TO %s' % pg_quote_identifier(self.module.params['rename_to'], 'sequence')) return exec_sql(self, ' '.join(query), ddl=True)
def drop(self, cascade=False): if not self.exists: return False query = "DROP TABLE %s" % pg_quote_identifier(self.name, 'table') if cascade: query += " CASCADE" return exec_sql(self, query, ddl=True)
def drop(self): """Drop tablespace. Return True if success, otherwise, return False. """ return exec_sql(self, "DROP TABLESPACE %s" % pg_quote_identifier(self.name, 'database'), ddl=True)
def drop(self): """Implements DROP SEQUENCE command behavior.""" query = ['DROP SEQUENCE'] query.append(self.__add_schema()) if self.module.params.get('cascade'): query.append('CASCADE') return exec_sql(self, ' '.join(query), ddl=True)
def drop(self): if not self.exists: return False query = "SELECT pg_drop_replication_slot(%(name)s)" self.changed = exec_sql(self, query, query_params={'name': self.name}, ddl=True)
def __slot_exists(self): query = "SELECT slot_type FROM pg_replication_slots WHERE slot_name = %(name)s" res = exec_sql(self, query, query_params={'name': self.name}, add_to_executed=False) if res: self.exists = True self.kind = res[0][0]
def get_pg_version(self): query = "SELECT version()" raw = exec_sql(self, query, add_to_executed=False)[0][0] if raw: self.is_available = True raw = raw.split()[1].split('.') self.version = dict( major=int(raw[0]), minor=int(raw[1]), )
def create(self, tblname, idxtype, columns, cond, tblspace, storage_params, concurrent=True, unique=False): """Create PostgreSQL index. Return True if success, otherwise, return False. Args: tblname (str) -- name of a table for the index idxtype (str) -- type of the index like BTREE, BRIN, etc columns (str) -- string of comma-separated columns that need to be covered by index tblspace (str) -- tablespace for storing the index storage_params (str) -- string of comma-separated storage parameters Kwargs: concurrent (bool) -- build index in concurrent mode, default True """ if self.exists: return False if idxtype is None: idxtype = "BTREE" query = 'CREATE' if unique: query += ' UNIQUE' query += ' INDEX' if concurrent: query += ' CONCURRENTLY' query += ' %s' % self.name if self.schema: query += ' ON %s.%s ' % (self.schema, tblname) else: query += 'public.%s ' % tblname query += 'USING %s (%s)' % (idxtype, columns) if storage_params: query += ' WITH (%s)' % storage_params if tblspace: query += ' TABLESPACE %s' % tblspace if cond: query += ' WHERE %s' % cond self.executed_query = query if exec_sql(self, query, ddl=True, add_to_executed=False): return True return False
def create(self, kind='physical', immediately_reserve=False, output_plugin=False, just_check=False): if self.exists: if self.kind == kind: return False else: self.module.warn("slot with name '%s' already exists " "but has another type '%s'" % (self.name, self.kind)) return False if just_check: return None if kind == 'physical': # Check server version (needs for immedately_reserverd needs 9.6+): if self.cursor.connection.server_version < 96000: query = "SELECT pg_create_physical_replication_slot(%(name)s)" else: query = "SELECT pg_create_physical_replication_slot(%(name)s, %(i_reserve)s)" self.changed = exec_sql(self, query, query_params={ 'name': self.name, 'i_reserve': immediately_reserve }, ddl=True) elif kind == 'logical': query = "SELECT pg_create_logical_replication_slot(%(name)s, %(o_plugin)s)" self.changed = exec_sql(self, query, query_params={ 'name': self.name, 'o_plugin': output_plugin }, ddl=True)
def create(self, location): """Create tablespace. Return True if success, otherwise, return False. args: location (str) -- tablespace directory path in the FS """ query = ("CREATE TABLESPACE %s LOCATION '%s'" % (pg_quote_identifier(self.name, 'database'), location)) return exec_sql(self, query, ddl=True)
def __set_setting(self, setting): """Set tablespace setting. Return True if success, otherwise, return False. args: setting (str) -- string in format "setting_name = 'setting_value'" """ query = "ALTER TABLESPACE %s SET (%s)" % (pg_quote_identifier( self.name, 'database'), setting) return exec_sql(self, query, ddl=True)
def __get_tables_pub_info(self): """Get and return tables that are published by the publication. Returns: List of dicts with published tables. """ query = ("SELECT schemaname, tablename " "FROM pg_publication_tables WHERE pubname = %(pname)s") return exec_sql(self, query, query_params={'pname': self.name}, add_to_executed=False)
def rename(self, newname): """Rename tablespace. Return True if success, otherwise, return False. args: newname (str) -- new name for the tablespace" """ query = "ALTER TABLESPACE %s RENAME TO %s" % (pg_quote_identifier( self.name, 'database'), newname) self.new_name = newname return exec_sql(self, query, ddl=True)
def __check_table(self, table): """Check table or SQL in transaction mode for check_mode. Return True if it is OK. Arguments: table (str) - Table name that needs to be checked. It can be SQL SELECT statement that was passed instead of the table name. """ if 'SELECT ' in table.upper(): # In this case table is actually SQL SELECT statement. # If SQL fails, it's handled by exec_sql(): exec_sql(self, table, add_to_executed=False) # If exec_sql was passed, it means all is OK: return True exec_sql(self, 'SELECT 1 FROM %s' % pg_quote_identifier(table, 'table'), add_to_executed=False) # If SQL was executed successfully: return True
def create_like(self, src_table, including='', tblspace='', unlogged=False, params='', owner=''): """ Create table like another table (with similar DDL). Arguments: src_table - source table. including - corresponds to optional INCLUDING expression in CREATE TABLE ... LIKE statement. params - storage params (passed by "WITH (...)" in SQL), comma separated. tblspace - tablespace. owner - table owner. unlogged - create unlogged table. """ changed = False name = pg_quote_identifier(self.name, 'table') query = "CREATE" if unlogged: query += " UNLOGGED TABLE %s" % name else: query += " TABLE %s" % name query += " (LIKE %s" % pg_quote_identifier(src_table, 'table') if including: including = including.split(',') for i in including: query += " INCLUDING %s" % i query += ')' if params: query += " WITH (%s)" % params if tblspace: query += " TABLESPACE %s" % pg_quote_identifier( tblspace, 'database') if exec_sql(self, query, ddl=True): changed = True if owner: changed = self.set_owner(owner) return changed
def set_owner(self, new_owner): """Set tablespace owner. Return True if success, otherwise, return False. args: new_owner (str) -- name of a new owner for the tablespace" """ if new_owner == self.owner: return False query = "ALTER TABLESPACE %s OWNER TO %s" % (pg_quote_identifier( self.name, 'database'), new_owner) return exec_sql(self, query, ddl=True)
def __get_general_pub_info(self): """Get and return general publication information. Returns: Dict with publication information if successful, False otherwise. """ # Check pg_publication.pubtruncate exists (supported from PostgreSQL 11): pgtrunc_sup = exec_sql(self, ("SELECT 1 FROM information_schema.columns " "WHERE table_name = 'pg_publication' " "AND column_name = 'pubtruncate'"), add_to_executed=False) if pgtrunc_sup: query = ( "SELECT r.rolname AS pubowner, p.puballtables, p.pubinsert, " "p.pubupdate , p.pubdelete, p.pubtruncate FROM pg_publication AS p " "JOIN pg_catalog.pg_roles AS r " "ON p.pubowner = r.oid " "WHERE p.pubname = %(pname)s") else: query = ( "SELECT r.rolname AS pubowner, p.puballtables, p.pubinsert, " "p.pubupdate , p.pubdelete FROM pg_publication AS p " "JOIN pg_catalog.pg_roles AS r " "ON p.pubowner = r.oid " "WHERE p.pubname = %(pname)s") result = exec_sql(self, query, query_params={'pname': self.name}, add_to_executed=False) if result: return result[0] else: return False
def __is_owner(self): """Return True if self.role is the current object owner.""" if self.obj_type == 'table': query = ("SELECT 1 FROM pg_tables " "WHERE tablename = %(obj_name)s " "AND tableowner = %(role)s") elif self.obj_type == 'database': query = ("SELECT 1 FROM pg_database AS d " "JOIN pg_roles AS r ON d.datdba = r.oid " "WHERE d.datname = %(obj_name)s " "AND r.rolname = %(role)s") elif self.obj_type == 'function': query = ("SELECT 1 FROM pg_proc AS f " "JOIN pg_roles AS r ON f.proowner = r.oid " "WHERE f.proname = %(obj_name)s " "AND r.rolname = %(role)s") elif self.obj_type == 'sequence': query = ("SELECT 1 FROM pg_class AS c " "JOIN pg_roles AS r ON c.relowner = r.oid " "WHERE c.relkind = 'S' AND c.relname = %(obj_name)s " "AND r.rolname = %(role)s") elif self.obj_type == 'schema': query = ("SELECT 1 FROM information_schema.schemata " "WHERE schema_name = %(obj_name)s " "AND schema_owner = %(role)s") elif self.obj_type == 'tablespace': query = ("SELECT 1 FROM pg_tablespace AS t " "JOIN pg_roles AS r ON t.spcowner = r.oid " "WHERE t.spcname = %(obj_name)s " "AND r.rolname = %(role)s") elif self.obj_type == 'view': query = ("SELECT 1 FROM pg_views " "WHERE viewname = %(obj_name)s " "AND viewowner = %(role)s") elif self.obj_type == 'matview': query = ("SELECT 1 FROM pg_matviews " "WHERE matviewname = %(obj_name)s " "AND matviewowner = %(role)s") query_params = {'obj_name': self.obj_name, 'role': self.role} return exec_sql(self, query, query_params, add_to_executed=False)
def __get_rel_info(self): """Get and return state of relations replicated by the subscription. Returns: List of dicts containing relations state if successful, False otherwise. """ query = ("SELECT c.relname, r.srsubstate, r.srsublsn " "FROM pg_catalog.pg_subscription_rel r " "JOIN pg_catalog.pg_subscription s ON s.oid = r.srsubid " "JOIN pg_catalog.pg_class c ON c.oid = r.srrelid " "WHERE s.subname = %(name)s") result = exec_sql(self, query, query_params={'name': self.name}, add_to_executed=False) if result: return [dict(row) for row in result] else: return False
def get_stat(self): """Get and return index statistics. Return index statistics dictionary if index exists, otherwise False. """ query = ("SELECT * FROM pg_stat_user_indexes " "WHERE indexrelname = %(name)s " "AND schemaname = %(schema)s") result = exec_sql(self, query, query_params={ 'name': self.name, 'schema': self.schema }, add_to_executed=False) if result: return [dict(row) for row in result] else: return False