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)' )