def _migrate_for_pg_attributes_update(self, ignore, only):
with connection.temporary_connection() as cursor:
for namespace, table, column, name, definition in self._find_constraints(
):
if only and name not in only:
continue
if ignore and name in ignore:
continue
cursor.execute(
'UPDATE pg_catalog.pg_attribute SET attnotnull = TRUE '
'WHERE attrelid = \'%(ns)s.%(table)s\'::regclass::oid '
'AND attname = replace(\'%(column)s\', \'"\', \'\')' % {
'ns': connection.ops.quote_name(namespace),
'table': connection.ops.quote_name(table),
'column': connection.ops.quote_name(column),
})
cursor.execute(
'ALTER TABLE %(ns)s.%(table)s DROP CONSTRAINT %(name)s' % {
'ns': connection.ops.quote_name(namespace),
'table': connection.ops.quote_name(table),
'name': connection.ops.quote_name(name),
})
self.stdout.write(
'%(ns)s.%(table)s %(name)s %(definition)s -> %(ns)s.%(table)s.%(column)s NOT NULL'
% {
'ns': connection.ops.quote_name(namespace),
'table': connection.ops.quote_name(table),
'column': connection.ops.quote_name(column),
'name': connection.ops.quote_name(name),
'definition': definition,
})
def _migrate_for_postgres_12(self, ignore, only):
with connection.temporary_connection() as cursor:
for namespace, table, column, name, definition in self._find_constraints(
):
if only and name not in only:
continue
if ignore and name in ignore:
continue
cursor.execute(
'ALTER TABLE %(ns)s.%(table)s ALTER COLUMN %(column)s SET NOT NULL'
% {
'ns': connection.ops.quote_name(namespace),
'table': connection.ops.quote_name(table),
'column': connection.ops.quote_name(column),
})
cursor.execute(
'ALTER TABLE %(ns)s.%(table)s DROP CONSTRAINT %(name)s' % {
'ns': connection.ops.quote_name(namespace),
'table': connection.ops.quote_name(table),
'name': connection.ops.quote_name(name),
})
self.stdout.write(
'%(ns)s.%(table)s %(name)s %(definition)s -> %(ns)s.%(table)s.%(column)s NOT NULL'
% {
'ns': connection.ops.quote_name(namespace),
'table': connection.ops.quote_name(table),
'column': connection.ops.quote_name(column),
'name': connection.ops.quote_name(name),
'definition': definition,
})
def handle(self, *args, **kwargs):
while True:
sleep(0.5)
try:
with connection.temporary_connection():
self.stdout.write(self.style.SUCCESS("Connected to db."))
break
except OperationalError:
self.stdout.write(
self.style.WARNING("Still waiting for db..."))
def _can_update_pg_attribute(self):
sql = ("SELECT 1 "
"FROM information_schema.table_privileges "
"WHERE table_schema = 'pg_catalog' "
"AND table_name = 'pg_attribute' "
"AND privilege_type = 'UPDATE'")
with connection.temporary_connection() as cursor:
cursor.execute(sql)
result = cursor.fetchone()
return result is not None
def terminate_rogue_database_activity():
"""Terminate rogue database activity.
This excludes itself, naturally, and also auto-vacuum activity which is
governed by PostgreSQL and not something to be concerned about.
:return: A set of PIDs that could not be terminated, presumably because
they're running under a different role and we're not a superuser.
"""
with connection.temporary_connection() as cursor:
cursor.execute("""\
SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid != pg_backend_pid()
AND query NOT LIKE 'autovacuum:%'
""")
return {pid for pid, success in cursor if not success}
def get_rogue_database_activity():
"""Return details of rogue database activity.
This excludes itself, naturally, and also auto-vacuum activity which is
governed by PostgreSQL and not something to be concerned about.
:return: A list of dicts, where each dict represents a complete row from
the ``pg_stat_activity`` table, mapping column names to values.
"""
with connection.temporary_connection() as cursor:
cursor.execute("""\
SELECT * FROM pg_stat_activity
WHERE pid != pg_backend_pid()
AND query NOT LIKE 'autovacuum:%'
""")
names = tuple(column.name for column in cursor.description)
return [dict(zip(names, row)) for row in cursor]
def _find_constraints(self):
sql = (
"SELECT connamespace::regnamespace, conrelid::regclass, conname, pg_get_constraintdef(oid) "
"FROM pg_catalog.pg_constraint "
"WHERE connamespace::regnamespace NOT IN ('pg_catalog', 'information_schema') "
"AND contype = 'c' "
"AND conname LIKE '%_notnull'")
constraints = []
with connection.temporary_connection() as cursor:
cursor.execute(sql)
result = cursor.fetchmany()
for namespace, table, name, definition in result:
match = self.CHECK_CONSTRAINT_REGEXP.match(definition)
if match:
column, = match.groups()
constraints.append(
(namespace, table, column, name, definition))
return sorted(constraints)
def assert_constraints(null, check):
with connection.temporary_connection() as cursor:
cursor.execute(
'SELECT COUNT(*) FROM pg_catalog.pg_attribute '
'WHERE attnotnull = TRUE '
'AND attrelid = \'old_notnull_check_constraint_migration_app_testtable\'::regclass::oid '
'AND attname = \'field\'')
assert cursor.fetchone()[0] == null
cursor.execute(
'SELECT COUNT(*) FROM pg_catalog.pg_constraint '
'WHERE contype = \'c\''
'AND conrelid = \'old_notnull_check_constraint_migration_app_testtable\'::regclass::oid'
)
assert cursor.fetchone()[0] == check
with pytest.raises(IntegrityError):
cursor.execute(
'INSERT INTO old_notnull_check_constraint_migration_app_testtable VALUES (2, null)'
)
