rake db:schema:load fails about scenic HOT 12 CLOSED

Hmmm, I've never tried loading schema on a populated database. Is that supposed to work? I thought you were supposed to reset the database instead?

from scenic.

When I do it, Rails loads an empty database using the schema without building it from migrations.

On Wednesday, December 17, 2014 at 14:45, Derek Prior wrote:

Hmmm, I've never tried loading schema on a populated database. Is that supposed to work? I thought you were supposed to reset the database instead?

—
Reply to this email directly or view it on GitHub (#55 (comment)).

from scenic.

My guess is that however it is emptying the database is missing the views. We're missing yet another thing we have to monkey patch into...

from scenic.

Had the same problem again:

1. Tried to run tests before running migrations
2. Ran migrations after receiving an exception
3. Test database didn't update
4. Ran db:test:load to try to fix it
5. Failure is because a create_view statement is in my schema.rb file that is trying to recreate :pg_stat_statements, but the view already exists.

Here's the statement:

 create_view :pg_stat_statements, sql_definition:<<-SQL
       SELECT pg_stat_statements.userid,
 pg_stat_statements.dbid,
 pg_stat_statements.query,
 pg_stat_statements.calls,
 pg_stat_statements.total_time,
 pg_stat_statements.rows,
 pg_stat_statements.shared_blks_hit,
 pg_stat_statements.shared_blks_read,
 pg_stat_statements.shared_blks_dirtied,
 pg_stat_statements.shared_blks_written,
 pg_stat_statements.local_blks_hit,
 pg_stat_statements.local_blks_read,
 pg_stat_statements.local_blks_dirtied,
 pg_stat_statements.local_blks_written,
 pg_stat_statements.temp_blks_read,
 pg_stat_statements.temp_blks_written,
 pg_stat_statements.blk_read_time,
 pg_stat_statements.blk_write_time
FROM pg_stat_statements() pg_stat_statements(userid, dbid, query, calls, total_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);

It looks like all of that was added when I started using scenic. I think the solution may be to have the create_view drop any view with that name that already exists, then rebuild it?

from scenic.

This looks like a problem with the schema dumper. We don't create any views by default. We definitely don't want to dump that view. We should avoid dumping any internal views.

What version of postgres are you using?

from scenic.

[ ~ ] ✔ postgres --version
postgres (PostgreSQL) 9.3.5

@derekprior, I looked at my schema.rb file's git history, and the pg_stat_statements Postgres extension got removed by the schema dumper, and corresponding code for the pg_stat_statements view got added. It looks like the change is being made by adding scenic and running a migration.

EDIT: OK, I just to be sure, I rewound my application to before I added scenic, and dropped the database and rebuilt it from prior migrations so that it was in the state I had it in pre-scenic.

Confirmed that adding my first scenic view and migrating to incorporate the view causes the pg_stat_statements extension to be dropped from the top of my schema, and a create_view statement for the extension's view to get added to the schema file.

from scenic.

@derekprior, it might be a little bit restrictive, but maybe the approach here is to filter out any views starting with pg_ in the Scenic schema dumper, assuming those are all Postgres views for metadata, etc.

The PG documentation lists what are called "system views", which all follow this convention. I'm not sure if there's a straightforward way to otherwise determine views that are internal or related to extensions other than manually specifying them in ActiveRecord::SchemaDumper.ignore_tables.

from scenic.

@derekprior, I added #60, which filters out Postgres system views, as a possible solution.

from scenic.

Do the changes in 325988a now fix this problem?

from scenic.

@halogenandtoast, I just dumped my schema using master branch as of 325988a, and the schema dump doesn't contain pg_stat_statements, so I'm through my obstacle.

from scenic.

This will be in 0.3.0 releasing momentarily.

from scenic.

@derekprior, thanks for turning this around quickly!

from scenic.