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)