Home > Software engineering >  Why can I no longer create PostGIS database extensions on Heroku PostgreSQL?
Why can I no longer create PostGIS database extensions on Heroku PostgreSQL?

Time:08-11

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?

CodePudding user response:

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}
  • Related