PGExtension replace `create` with `create if not exists`

[aringot]

Hi, would it be possible to replace PGExtension's create with a create if not exists? (and maybe replace drop with drop if exists?

For some reason the extension I need already exists on my production instance but I would still want the migration to install it if it is not yet here (e.g. locally).

[olirice]

good point, its common for there to be differences. Docker Postgres, Supabase and RDS all ship with different extensions enabled by default. I can see how that'd be an issue if e.g. local development is docker and prod is Supabase

Unfortunately, I don't think we can do it because extensions can be schema qualified but are global to the database.

So if you specified

PGExtension(schema="extensions", signature="uuid-ossp")

but it was pre-enabled on your prod server in the public schema, you'd end up in a situation like this

initial state:

create extension "uuid-ossp" schema public;

alembic_utils emits:

create extension if not exists "uuid-ossp" schema extensions;

In that case, the migration would run without issue but "uuid-ossp" wouldn't be installed where you expected

NOTICE:  extension "uuid-ossp" already exists, skipping
CREATE EXTENSION

---

If you're encountering this issue I'd recommend manually adding this to the beginning of your first migration to normalize any differences in your environments and letting alembic_utils take over from there

def upgrade():
    op.execute('drop extension if exists "uuid-ossp"')

[aringot]

Thanks for the quick answer! Helped me realize that this was maybe not the actual problem. The migration does seem to work when applied manually, only the pytest-alembic test model_definitions_match_ddl does not pass for some reason... It looks like it tries dropping the extension (which does not work due to a psycopg2.errors.InternalError_: cache lookup failed for function 19234) and then tries creating the extension, which fails because it still exists 🤷

I guess I will ask for help in the pytest-alembic repo.

[olirice]

ok cool closing this for now, but feel free to continue commenting if anything comes up

[aringot]

For reference: we fixed the issue by explicitly defining the dependency on this extension for the index using it: op.execute('ALTER INDEX ix_nodes_path DEPENDS ON EXTENSION ltree'). After that the pytest-alembic test managed to drop and recreate the extension without issues.

Thanks again for your support!