DEV: correct drop logic for columns in post table
We need to recreate the badge_posts view each time we change column on the posts table.
The p.* is auto expanded on view create, cascade should not have been used
diff --git a/db/post_migrate/20200430010528_remove_avg_time_from_topics_posts.rb b/db/post_migrate/20200430010528_remove_avg_time_from_topics_posts.rb index 848f9e5..6c150b2 100644 --- a/db/post_migrate/20200430010528_remove_avg_time_from_topics_posts.rb +++ b/db/post_migrate/20200430010528_remove_avg_time_from_topics_posts.rb @@ -6,12 +6,33 @@ class RemoveAvgTimeFromTopicsPosts < ActiveRecord::Migration[6.0] def up # this makes it re-runnable and also works if it was not created initially execute <<~SQL - ALTER TABLE topics DROP COLUMN IF EXISTS avg_time CASCADE + ALTER TABLE topics DROP COLUMN IF EXISTS avg_time SQL - execute <<~SQL - ALTER TABLE posts DROP COLUMN IF EXISTS avg_time CASCADE - SQL + ActiveRecord::Base.transaction do + execute "DROP VIEW badge_posts" + + execute <<~SQL + ALTER TABLE posts DROP COLUMN IF EXISTS avg_time + SQL + + # we must recreate this view every time we amend posts + # p.* is auto expanded and persisted into the view definition + # at create time + execute <<~SQL + CREATE VIEW badge_posts AS + SELECT p.* + FROM posts p + JOIN topics t ON t.id = p.topic_id + JOIN categories c ON c.id = t.category_id + WHERE c.allow_badges AND + p.deleted_at IS NULL AND + t.deleted_at IS NULL AND + NOT c.read_restricted AND + t.visible AND + p.post_type IN (1,2,3) + SQL + end end def down
GitHub sha: 53e0d8fb