Rails db:structure:dump has incorrect search paths

Question

I am using db/structure.sql to preserve our DB State as we have PostGIS extensions and built in functions that make using schema.rb impractical.

When running db:structure:dump rails has the odd behaviour of setting search paths near the top AND near the bottom of the file. The problem here is that the search path at the top is incorrect, causing db:schema:load to fail miserably.

I am editing it manually at the moment (i.e. adding postgis to the top search path), but it would be nice if I could somehow get the search path set correctly by the dump task.

database.yml

development: &dev
  adapter: postgis
  database: myapp_dev
  host: localhost
  encoding: utf8
  template: template0 # Required for UTF8 encoding
  postgis_extension: true
  schema_search_path: "public,postgis"

db/structure.sql

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

... Table / Sequence / Index creation happens here ...

--
-- PostgreSQL database dump complete
--

SET search_path TO public,postgis;

... Migrations inserted here ...

The problem here, is that the tables need postgis in the search path to be created (they do use postgis data types after all)

I presume the second search path set is added as a result of the search paths set in database.yml.

Is it possible to get rails to put the correct search path at the top of the file?

Can you set the search path in the database configs rather than the Rails configs? — mu is too short, Jan 13 '16 at 07:20
I can (ALTER DATABASE db SET search_path=public,postgis;) But the point of structure.sql is to allow a new deployment to have a working environment without manual sql commands (otherwise I could just use pg_dump and pg_restore). — Chris Noldus, Jan 14 '16 at 23:47
AFAIK `structure.sql` is generated by shelling out to the PostgreSQL CLI tools, the Rails configuration isn't involved beyond specifying the basic connection parameters. Anyway, if you do an ALTER DATABASE, that should show up in `structure.sql`, no? — mu is too short, Jan 14 '16 at 23:59
No, even after ALTER DATABASE, structure.sql only has the postgis search path at the end - this is annoying because everytime I run a migration I have to manually re-add it to the file so that my specs pass. — Chris Noldus, Jan 27 '16 at 23:34
you can research github issues https://github.com/rgeo/activerecord-postgis-adapter/issues also see: https://gist.github.com/stestaub/6118568 — Marat Amerov, Jan 28 '16 at 14:58

score 1 · Answer 1 · answered Jul 12 '16 at 15:01

I have made test project and repeated sequence of actions described in question - everything works fine! I noticed regularity - structure.sql contains code:

SET search_path = public, pg_catalog;

CREATE TABLE spatial_tests (
    id integer NOT NULL,
    latlon postgis.geography(Point,4326),
    geo_col postgis.geometry(Geometry,4326)
);

Pay attention to postgis prefix at column’s types. Code like this happens only if postgis extension lives in postgis schema:

postgis_test=# \dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 2.1.7   | postgis    | PostGIS geometry, geography, and raster spatial types and functions
(2 rows)

And this code executes fine. In another case, when postgis extension lives in public scheme, structure.sql looks like this:

SET search_path = public, pg_catalog;

CREATE TABLE spatial_tests (
    id integer NOT NULL,
    latlon geography(Point,4326),
    geo_col geometry(Geometry,4326)
);

Here no prefix in column’s names and this code generates errors on execution.

Chris Noldus, please check which scheme contains postgis extension in database where you makes dump (you can make it by \dx command in psql console) - may be it’s cause of problem.

Situation like this may happens after creation of database by rake db:create without postgis_schema: postgis in database.yml. You can read details about this option in activerecord-postgis-adapter documentation.

Thanks for your answer. I don't have the issue anymore, and I'm not really sure what fixed it - since the question was asked I have updated the libraries and database engine, so I presume one of these updates fixed the problem. — Chris Noldus, Aug 15 '16 at 22:54

Rails db:structure:dump has incorrect search paths

1 Answers1