def create_index(conn: LoggingDatabaseConnection) -> None: conn.rollback() # we have to set autocommit, because postgres refuses to # CREATE INDEX CONCURRENTLY without it. conn.set_session(autocommit=True) try: c = conn.cursor() # if we skipped the conversion to GIST, we may already/still # have an event_search_fts_idx; unfortunately postgres 9.4 # doesn't support CREATE INDEX IF EXISTS so we just catch the # exception and ignore it. import psycopg2 try: c.execute("CREATE INDEX CONCURRENTLY event_search_fts_idx" " ON event_search USING GIN (vector)") except psycopg2.ProgrammingError as e: logger.warning( "Ignoring error %r when trying to switch from GIST to GIN", e) # we should now be able to delete the GIST index. c.execute("DROP INDEX IF EXISTS event_search_fts_idx_gist") finally: conn.set_session(autocommit=False)
def create_index(conn: LoggingDatabaseConnection) -> None: conn.rollback() conn.set_session(autocommit=True) c = conn.cursor() # We create with NULLS FIRST so that when we search *backwards* # we get the ones with non null origin_server_ts *first* c.execute( "CREATE INDEX CONCURRENTLY event_search_room_order ON event_search(" "room_id, origin_server_ts NULLS FIRST, stream_ordering NULLS FIRST)" ) c.execute( "CREATE INDEX CONCURRENTLY event_search_order ON event_search(" "origin_server_ts NULLS FIRST, stream_ordering NULLS FIRST)" ) conn.set_session(autocommit=False)
def reindex_txn(conn: LoggingDatabaseConnection) -> None: conn.rollback() if isinstance(self.database_engine, PostgresEngine): # postgres insists on autocommit for the index conn.set_session(autocommit=True) try: txn = conn.cursor() txn.execute( "CREATE INDEX CONCURRENTLY state_groups_state_type_idx" " ON state_groups_state(state_group, type, state_key)") txn.execute("DROP INDEX IF EXISTS state_groups_state_id") finally: conn.set_session(autocommit=False) else: txn = conn.cursor() txn.execute( "CREATE INDEX state_groups_state_type_idx" " ON state_groups_state(state_group, type, state_key)") txn.execute("DROP INDEX IF EXISTS state_groups_state_id")
def prepare_database( db_conn: LoggingDatabaseConnection, database_engine: BaseDatabaseEngine, config: Optional[HomeServerConfig], databases: Collection[str] = ("main", "state"), ): """Prepares a physical database for usage. Will either create all necessary tables or upgrade from an older schema version. If `config` is None then prepare_database will assert that no upgrade is necessary, *or* will create a fresh database if the database is empty. Args: db_conn: database_engine: config : application config, or None if we are connecting to an existing database which we expect to be configured already databases: The name of the databases that will be used with this physical database. Defaults to all databases. """ try: cur = db_conn.cursor(txn_name="prepare_database") # sqlite does not automatically start transactions for DDL / SELECT statements, # so we start one before running anything. This ensures that any upgrades # are either applied completely, or not at all. # # (psycopg2 automatically starts a transaction as soon as we run any statements # at all, so this is redundant but harmless there.) cur.execute("BEGIN TRANSACTION") logger.info("%r: Checking existing schema version", databases) version_info = _get_or_create_schema_state(cur, database_engine) if version_info: user_version, delta_files, upgraded = version_info logger.info( "%r: Existing schema is %i (+%i deltas)", databases, user_version, len(delta_files), ) # config should only be None when we are preparing an in-memory SQLite db, # which should be empty. if config is None: raise ValueError( "config==None in prepare_database, but database is not empty" ) # if it's a worker app, refuse to upgrade the database, to avoid multiple # workers doing it at once. if config.worker_app is not None and user_version != SCHEMA_VERSION: raise UpgradeDatabaseException( OUTDATED_SCHEMA_ON_WORKER_ERROR % (SCHEMA_VERSION, user_version)) _upgrade_existing_database( cur, user_version, delta_files, upgraded, database_engine, config, databases=databases, ) else: logger.info("%r: Initialising new database", databases) # if it's a worker app, refuse to upgrade the database, to avoid multiple # workers doing it at once. if config and config.worker_app is not None: raise UpgradeDatabaseException(EMPTY_DATABASE_ON_WORKER_ERROR) _setup_new_database(cur, database_engine, databases=databases) # check if any of our configured dynamic modules want a database if config is not None: _apply_module_schemas(cur, database_engine, config) cur.close() db_conn.commit() except Exception: db_conn.rollback() raise