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?

Answer 1

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.