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 = '%s'" % self.name) 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 = '%s'" % self.name) result = exec_sql(self, query, add_to_executed=False) if result: return result[0] else: return False
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 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 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 = '%s'" % self.name) res = exec_sql(self, query, 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 get_pg_version(self): query = "SELECT version()" raw = exec_sql(self, query, add_to_executed=False)[0][0] if not raw: return self.is_available = True full = raw.split()[1] tmp = full.split('.') major = int(tmp[0]) minor = int(tmp[1].rstrip(',')) patch = None if len(tmp) >= 3: patch = int(tmp[2].rstrip(',')) self.version = dict( major=major, minor=minor, full=full, raw=raw, ) if patch is not None: self.version['patch'] = patch
def set_owner(self): """Implements ALTER SEQUENCE OWNER TO command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append('OWNER TO "%s"' % self.module.params['owner']) return exec_sql(self, ' '.join(query), return_bool=True)
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 __get_general_subscr_info(self): """Get and return general subscription information. Returns: Dict with subscription information if successful, False otherwise. """ query = ("SELECT d.datname, r.rolname, s.subenabled, " "s.subconninfo, s.subslotname, s.subsynccommit, " "s.subpublications FROM pg_catalog.pg_subscription s " "JOIN pg_catalog.pg_database d " "ON s.subdbid = d.oid " "JOIN pg_catalog.pg_roles AS r " "ON s.subowner = r.oid " "WHERE s.subname = %(name)s AND d.datname = %(db)s") result = exec_sql(self, query, query_params={ 'name': self.name, 'db': self.db }, add_to_executed=False) if result: return result[0] else: return False
def set_schema(self): """Implements ALTER SEQUENCE SET SCHEMA command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append('SET SCHEMA "%s"' % self.module.params['newschema']) return exec_sql(self, ' '.join(query), return_bool=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), return_bool=True)
def rename(self): """Implements ALTER SEQUENCE RENAME TO command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append('RENAME TO "%s"' % self.module.params['rename_to']) return exec_sql(self, ' '.join(query), return_bool=True)
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 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, return_bool=True)
def __fill_out_info(self, result, info_key=None, schema_key=None, name_key=None): # Convert result to list of dicts to handle it easier: result = [dict(row) for row in result] for elem in result: # Add schema name as a key if not presented: if not self.info[info_key].get(elem[schema_key]): self.info[info_key][elem[schema_key]] = {} # Add object name key as a subkey # (they must be uniq over a schema, so no need additional checks): self.info[info_key][elem[schema_key]][elem[name_key]] = {} # Add other other attributes to a certain index: for key, val in iteritems(elem): if key not in (schema_key, name_key): self.info[info_key][elem[schema_key]][ elem[name_key]][key] = val if info_key in ('tables', 'indexes'): schemaname = elem[schema_key] if self.schema: schemaname = self.schema relname = '%s.%s' % (schemaname, elem[name_key]) result = exec_sql(self, "SELECT pg_relation_size (%s)", query_params=(relname, ), add_to_executed=False) self.info[info_key][elem[schema_key]][ elem[name_key]]['size'] = result[0][0] if info_key == 'tables': result = exec_sql(self, "SELECT pg_total_relation_size (%s)", query_params=(relname, ), add_to_executed=False) self.info[info_key][elem[schema_key]][ elem[name_key]]['total_size'] = result[0][0]
def drop(self): """Drop tablespace. Return True if success, otherwise, return False. """ return exec_sql(self, 'DROP TABLESPACE "%s"' % self.name, return_bool=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): 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}, return_bool=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), return_bool=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 __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 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 query += ' ON "%s"."%s" ' % (self.schema, 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 return exec_sql(self, query, return_bool=True, add_to_executed=False)
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 __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 = '%s'" % self.name) return exec_sql(self, query, add_to_executed=False)
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\'' % (self.name, location)) return exec_sql(self, query, return_bool=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)' % (self.name, setting) return exec_sql(self, query, return_bool=True)
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, 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 }, return_bool=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 }, return_bool=True)
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"' % (self.name, newname) self.new_name = newname return exec_sql(self, query, return_bool=True)