How to adjust an Elixir application to work with PostgreSQL extensions on Heroku placed in `heroku_ext` schema

Question

I have run in to this DB error message during the creation of a Heroku review app:

psql:/priv/repo/structure.sql:25: ERROR:  Extensions can only be created on heroku_ext schema
CONTEXT:  PL/pgSQL function inline_code_block line 7 at RAISE

This was caused by changes to PostgreSQL extension schema management made by Heroku which are effective since 01 August 2022.

The question is now how to best adjust an Elixir application to comply with the new Heroku rules? In particular when:

You create extensions in migrations;
You create extensions in priv/repo//structure.sql for mix ecto.load;
In your code you use functions from those extensions (e.g. UNACCENT()).

score 1 · Answer 1 · answered Aug 09 '22 at 09:41

Addressing the particular points:

If you create extensions in migrations – e.g. using execute "create extension if not exists \"uuid-ossp\"", ""; – you don't need to do anything: they will be created in the heroku_ext schema.
Make sure all your CREATE EXTENSION calls use WITH SCHEMA heroku_ext. E.g. CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA heroku_ext;.
To let PostgreSQL know that it should search also in the heroku_ext schema for extensions and therein present functions add after_connect: {Postgrex, :query!, _query_args = ["SET search_path TO public,heroku_ext", []]} to your Repo config (more: [1], [2]) or adjust it to have heroku_ext if you already had a custom search_path. E.g.

config :app, App.Repo,
  adapter: Ecto.Adapters.Postgres,
  # ...
  after_connect: {Postgrex, :query!, _query_args = ["SET search_path TO public,heroku_ext", []]}

Without the above you would need to call functions with a prefix/namespace qualifier e.g. heroku_ext.UNACCENT() which would break an app if it would have extensions placed in different schemas.

After doing those the review app should be created successfully and also the production environment – in which extensions might be still placed in different schemas (e.g. public or pg_catalog) – will still work fine.

It's helpful to connect to the review apps' or staging/production apps' DBs and list the extensions they have e.g.:

% heroku pg:psql -a app-pr-123
app-pr-123::DATABASE=> \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description                   
-----------+---------+------------+-------------------------------------------------
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 unaccent  | 1.1     | heroku_ext | text search dictionary that removes accents
 uuid-ossp | 1.1     | heroku_ext | generate universally unique identifiers (UUIDs)
(3 rows)

References

[1] https://hexdocs.pm/ecto/multi-tenancy-with-query-prefixes.html
[2] What is the schema search path search_path?
Same problem described for Ruby/Rails apps ERROR: extension "btree_gist" must be installed in schema "heroku_ext"

How to adjust an Elixir application to work with PostgreSQL extensions on Heroku placed in `heroku_ext` schema

1 Answers1