hstore extension not being dumped to structure.sql

[JonasNielsen]

Hi guys

I'm not sure if this is specific for the apartment gem, but there seem to be a bug with dumping the hstore extension to structure.sql. I've followed the guidelines on how to setup hstore in an hstore-specific schema, but are having trouble preparing the test database. The problem is that structure.sql is missing CREATE EXTENSION HSTORE SCHEMA hstore. It does, however, contain the hstore schema and the table with the hstore column.

Apartment configuration:

Apartment.configure do |config|
  config.database_names = ['da', 'se', 'de']
  config.prepend_environment = false
  config.default_schema = "da"
  config.persistent_schemas = ['hstore']
end

database.yml:

development:
  adapter: postgresql
  encoding: utf8
  host: localhost
  database: my_development
  username: postgres
  password: postgres
  template: template0 # Required for UTF8 encoding
  schema_search_path: "da,hstore" # default_schema, persistent_schema
  pool: 10

test:
  adapter: postgresql
  encoding: utf8
  host: localhost
  database: my_test
  username: postgres
  password: postgres
  template: template0 # Required for UTF8 encoding
  schema_search_path: "da,hstore"
  pool: 10

$ rake apartment:migrate succeeds as usual in development.

But, $ rake db:structure:dump only dumbs the hstore schema and table with hstore column, not the extension:

--
-- PostgreSQL database dump
--

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

--
-- Name: da; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA da;


--
-- Name: SCHEMA da; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA da IS 'standard public schema';


--
-- Name: hstore; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA hstore;


SET search_path = da, pg_catalog;

--
-- Name: _final_mode(anyarray); Type: FUNCTION; Schema: da; Owner: -
--

CREATE FUNCTION _final_mode(anyarray) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
      SELECT a
      FROM unnest($1) a
      GROUP BY 1
      ORDER BY COUNT(1) DESC, 1
      LIMIT 1;
    $_$;


--
-- Name: mode(anyelement); Type: AGGREGATE; Schema: da; Owner: -
--

CREATE AGGREGATE mode(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}',
    FINALFUNC = _final_mode
);


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: email_subscriptions; Type: TABLE; Schema: da; Owner: -; Tablespace: 
--

CREATE TABLE email_subscriptions (
    id integer NOT NULL,
    type character varying(255),
    search hstore.hstore,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

Naturally, this causes $ rake db:test:prepare to fail.

Is the setup guide in the readme incomplete? And how can I make the dump include the extension so that the test db can be prepared?

Thanks!

[bradrobertson]

hmm... i don't know that the schema dump is really responsible for creating
the extension itself. Extensions are really just addons and a one-time
thing, I wouldn't think a db dump should even know really what extensions
are present on a db.

And ya this isn't really apartment related, it's more to do with pg, hstore
and rails specifically.

In a related note though, I don't think we've talked with anyone who has
had much luck using a sql dump of their db with apartment due to the nature
of the schema usage. I can't remember exactly what the issues were, but we
still use schema.rb rather than a sql dump for loading in new tenants.

Brad Robertson |
Señor
Developer
**
Influitive | Mobilize Your Advocates

http://twitter.com/influitive | http://facebook.com/influitive
*

On Mon, May 20, 2013 at 8:39 AM, Jonas Bruun Nielsen <
notifications@github.com> wrote:

Hi guys

I'm not sure if this is specific for the apartment gem, but there seem to
be a bug with dumping the hstore extension to structure.sql. I've
followed the guidelines on how to setup hstorehttps://github.com/influitive/apartment#postgresql-schemasin an hstore-specific schema, but are having trouble preparing the test
database. The problem is that structure.sql is missing CREATE EXTENSION
HSTORE SCHEMA hstore. It does, however, contain the hstore schema and the
table with the hstore column.

Apartment configuration:

Apartment.configure do |config|
config.database_names = ['da', 'se', 'de']
config.prepend_environment = false
config.default_schema = "da"
config.persistent_schemas = ['hstore']end

database.yml:

development:
adapter: postgresql
encoding: utf8
host: localhost
database: my_development
username: postgres
password: postgres
template: template0 # Required for UTF8 encoding
schema_search_path: "da,hstore" # default_schema, persistent_schema
pool: 10
test:
adapter: postgresql
encoding: utf8
host: localhost
database: my_test
username: postgres
password: postgres
template: template0 # Required for UTF8 encoding
schema_search_path: "da,hstore"
pool: 10

$ rake apartment:migrate succeeds as usual in development.

But, $ rake db:structure:dump only dumbs the hstore schema and table with
hstore column, not the extension:

---- PostgreSQL database dump--
SET statement_timeout = 0;SET client_encoding = 'UTF8';SET standard_conforming_strings = on;SET check_function_bodies = false;SET client_min_messages = warning;
---- Name: da; Type: SCHEMA; Schema: -; Owner: ---
CREATE SCHEMA da;

---- Name: SCHEMA da; Type: COMMENT; Schema: -; Owner: ---
COMMENT ON SCHEMA da IS 'standard public schema';

---- Name: hstore; Type: SCHEMA; Schema: -; Owner: ---
CREATE SCHEMA hstore;

SET search_path = da, pg_catalog;
---- Name: final_mode(anyarray); Type: FUNCTION; Schema: da; Owner: ---
CREATE FUNCTION final_mode(anyarray) RETURNS anyelement
LANGUAGE sql IMMUTABLE
AS $$
SELECT a
FROM unnest($1) a
GROUP BY 1
ORDER BY COUNT(1) DESC, 1
LIMIT 1;
$$;

---- Name: mode(anyelement); Type: AGGREGATE; Schema: da; Owner: ---
CREATE AGGREGATE mode(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}',
FINALFUNC = _final_mode);

SET default_tablespace = '';
SET default_with_oids = false;
---- Name: email_subscriptions; Type: TABLE; Schema: da; Owner: -; Tablespace: --
CREATE TABLE email_subscriptions (
id integer NOT NULL,
type character varying(255),
search hstore.hstore,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL);

Naturally, this causes $ rake db:test:prepare to fail.

Is the setup guide in the readme incomplete? And how can I make the dump
include the extension so that the test db can be prepared?

Thanks!

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/51
.

[JonasNielsen]

Thanks for your feedback, Brad. It makes sense, that it's not an apartment issue. But I think it would be good with a note to the hstore installatiion guidelines.

Because the extension is installed in the hstore schema, CREATE EXTENSION HSTORE SCHEMA hstore has to run after the schema is created. This makes it impossible install the extension in the test database before rake db:test:prepare runs. Does that make sense?

Maybe I'm misunderstanding something, but I don't understand how you can have a working test environment with that configuration. Is schema.rb that different?

Btw, the reason our sql dump works with apartment is because of we only require one tenant in test env.

[JonasNielsen]

If anyone else are experiencing these problems I did not find a proper solution.
So here's a good old hack that you can add to a .rake file in your project:

namespace :db do
  namespace :structure do
    task :add_hstore_extension do
      puts "HACK! Adding `CREATE EXTENSION hstore SCHEMA hstore;` to structure.sql (see db_strucute_dump.rake)"

      structure_dump = Rails.root + "db/structure.sql"
      new_structure_dump = Rails.root + "db/new_structure.sql"

      new_file = File.open(new_structure_dump, 'w')
      existing = File.open(structure_dump)

      existing.each do |line|
        new_file << line
        if line == "CREATE SCHEMA hstore;\n"
          new_file << "CREATE EXTENSION hstore SCHEMA hstore;"
        end
      end

      existing.close
      new_file.close
      FileUtils.mv(new_structure_dump, structure_dump)
    end
  end
end

Rake::Task["db:structure:dump"].enhance do
  Rake::Task["db:structure:add_hstore_extension"].invoke
end

[pilap82]

Interesting, I have the opposite problem :)
I'm perfectly able to generate a proper structure.sql (rake db:structure:dump)... but I can't get rake db:schema:dump to create the tables that have an hstore column :(

[bradrobertson]

btw Rails 4 now directly supports extensions and it's dumped to the schema by default. I'm going to close this out as it's not really Apartment related anyway

[archonic]

I ran into this with Rails 5.2. I'm not sure why rails db:structure:dump doesn't write CREATE EXTENSION hstore... to structure.sql, but it doesn't.

The author of logidze helped me out with a PR in a minimal reproduction app:
archonic/logidze-apartment@1a1561b#diff-af7fc5449c5d64070f07544c5d9e5b10

The files lib/tasks/db_enhancements.rake patches the creation of the extension using rake tasks, and lib/tasks/logidze_apartment_verify.rake verifies if it's installed correctly.

[shimaamarzouk]

I believe you can create extensions yourself by adding the following to structure.sql:
CREATE EXTENSION IF NOT EXISTS HSTORE SCHEMA hstore;