Why can I no longer create PostGIS database extensions on Heroku PostgreSQL?

Question

Sometime in the last day or so, Heroku Postgres started giving this:

d8jk6gfhj88gfo=> CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
ERROR:  unterminated dollar-quoted string at or near "$topology$e#escape#$;
BEGIN
    IF ext_schema_name != 'topology' THEN
      RAISE EXCEPTION 'postgis_topology can only be created on topology schema';
    END IF;
END "
LINE 5:   ext_schema_name text := $e#escape#$topology$e#escape#$;
                                            ^
QUERY:  
DECLARE
  -- This is utterly bullshit, since we receive the schema name as an unsafe parameter I'm replacing the placeholder a random string.
  -- I'm not proud of it, but it's good enough until we get a fix from upstream.
  ext_schema_name text := $e#escape#$topology$e#escape#$;
BEGIN
    IF ext_schema_name != 'topology' THEN
      RAISE EXCEPTION 'postgis_topology can only be created on topology schema';
    END IF;
END

Any ideas of the cause, solution or a workaround?

Does this answer your question? [Heroku postgres postgis - django releases fail with: relation "spatial\_ref\_sys" does not exist](https://stackoverflow.com/questions/73206939/heroku-postgres-postgis-django-releases-fail-with-relation-spatial-ref-sys) — Doug Harris, Aug 04 '22 at 18:12
Thanks @DougHarris it does seem to describe similar issues, but unfortunately all of the "answers" there so far are sub-optimal workarounds. The initial response I received from Heroku Support pointed me to https://devcenter.heroku.com/changelog-items/2446 but even when using `heroku_ext` instead of `topology` I get the same error above (but with `heroku_ext` in the message) — DrMeers, Aug 04 '22 at 23:53
Yeah, I'm with you on sub-optimal workarounds. My app uses the `citext` extension and this change is affecting backups & restores for us. — Doug Harris, Aug 05 '22 at 13:48

score 0 · Accepted Answer · answered Aug 11 '22 at 02:34

Heroku have changed/fixed something in the past couple of days, but there is no additional entry in https://devcenter.heroku.com/changelog to say what they've actually done.

I still cannot get an unedited SQL file generated by pg_dump to load using psql like it used to, but if I strip out the WITH SCHEMA public directives, and replace all public.geometry with heroku_ext.geometry it now loads correctly:

sed "s/ WITH SCHEMA public//;s/public\.geometry/heroku_ext.geometry/" db.sql | psql ${DATABASE_URL}

Why can I no longer create PostGIS database extensions on Heroku PostgreSQL?

1 Answers1