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 __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'):
relname = elem[name_key]
schemaname = elem[schema_key]
if not self.schema:
result = exec_sql(self,
"SELECT pg_relation_size ('%s.%s')" %
(schemaname, relname),
add_to_executed=False)
else:
relname = '%s.%s' % (self.schema, relname)
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':
relname = elem[name_key]
schemaname = elem[schema_key]
if not self.schema:
result = exec_sql(
self,
"SELECT pg_total_relation_size ('%s.%s')" %
(schemaname, relname),
add_to_executed=False)
else:
relname = '%s.%s' % (self.schema, relname)
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 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 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 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 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 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_general_sub_info(self):
"""Get and return general subscription information.
Returns:
Dict with subscription information if successful, False otherwise.
"""
query = (" SELECT "
"r.rolname AS subowner, "
"s.subdbid, "
"s.subname, "
"s.subenabled, "
"s.subsynccommit, "
"s.subconninfo, "
"s.subslotname, "
"s.subpublications "
"FROM pg_subscription AS s "
"JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oid "
"WHERE s.subname = '%s'" % self.name)
result = exec_sql(self, query, add_to_executed=False)
pprint.pprint('temp log: result' + str(result))
if result:
return result[0]
else:
return False
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 __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 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 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 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('%s')" % self.name
else:
query = "SELECT pg_create_physical_replication_slot('%s', %s)" % (
self.name, immediately_reserve)
elif kind == 'logical':
query = "SELECT pg_create_logical_replication_slot('%s', '%s')" % (
self.name, output_plugin)
self.changed = exec_sql(self, 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 __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_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,
tblname,
idxtype,
columns,
cond,
tblspace,
storage_params,
concurrent=True):
"""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
changed = False
if idxtype is None:
idxtype = "BTREE"
query = 'CREATE 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 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):
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 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 __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_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 __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 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 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 get_tbl_stat(self):
"""Get table statistics and fill out self.info dictionary."""
if not self.schema:
query = "SELECT * FROM pg_stat_user_tables"
result = exec_sql(self, query, add_to_executed=False)
else:
query = "SELECT * FROM pg_stat_user_tables WHERE schemaname = %s"
result = exec_sql(self,
query,
query_params=(self.schema, ),
add_to_executed=False)
if not result:
return
self.__fill_out_info(result,
info_key='tables',
schema_key='schemaname',
name_key='relname')
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
