PostgreSQL database creation sets search_path to ag_catalog first, causing Alembic issues

[paudley]

Problem

When creating a PostgreSQL database with the AGE extension, the default search_path is set to:

ag_catalog, "$user", public

This causes issues with Alembic migrations because:

1. Alembic creates its alembic_version table during migrations
2. With ag_catalog first in the search path, the table is created in the ag_catalog schema instead of public
3. Subsequent migration operations fail because they look for alembic_version in the wrong schema

Error Observed

relation "alembic_version" does not exist
[SQL: INSERT INTO alembic_version (version_num) VALUES ('000000000000')]

The table exists but in ag_catalog schema, not public.

Current Workaround

In ThinIce, we added a workaround in alembic/env.py:

# CRITICAL: Reset search_path to public BEFORE configuring context.
# AGE extension may modify the default search_path, putting ag_catalog
# before public. This causes Alembic's alembic_version table to be
# created in the wrong schema.
connection.execute(text("SET search_path TO public"))

Suggested Fix

When creating databases that will use AGE, the search_path should be set to have public first:

ALTER DATABASE dbname SET search_path TO public, ag_catalog, "$user";

Or ensure the database creation script sets an appropriate default search_path.

Reference

• ThinIce Issue: https://github.com/paudley/thinice/issues/284
• Alembic documentation on search_path: https://alembic.sqlalchemy.org/en/latest/cookbook.html