Document issues with SELECT * in views

[derekprior]

I recently created a view that was meant to be the entirety of one table plus some aggregate data on each row. So something like this simplified, incomplete example:

SELECT
    posts.*,
    approved_comments.count AS approved_comments_count,
    deleted_comments.count AS deleted_comments_count,
FROM
    posts
LEFT JOIN (SELECT ... ) as approved_comments ON ...
LEFT JOIN (SELECT .. ) as deleted_comments ON ...

Then I created a model like so:

class PostWithStatistics < Post
end

This allows my PostWithStatistics to act as a Post model and lets me opt in to when I want the extra associated data. Everything works reasonably well until...

class AddFooToPosts < ActiveRecord::Migration
  def change
    add_column :posts, :foo, :string
  end
end

The posts table now has a new column and I'd expect my posts_with_statistics view to reflect that because I used SELECT *. Unfortunately, it doesn't work this way because when the view is created with SELECT *, Postgres bakes into the schema the actual column names represented by *. To pick up the new column I'd have to use update_view and pass the same version number that is already deployed.

For a similar reason, this migration would also be a problem

class RemoveBarFromPosts < ActiveRecord::Migration
  def change
    remove_column :posts, :bar, :string
  end
end

This migration will fail because Postgres knows that there's a view that explicitly lists the column bar as part of its schema. This dependency will prevent the column from being removed. The workaround is:

class RemoveBarFromPosts < ActiveRecord::Migration
  def up
    drop_view :posts_with_statistics
    remove_column :posts, :bar, :string
    create_view :posts_with_statistics
  end
end

I don't think there's anything we can do from the scenic perspective to handle this. I think we likely just need to document this (and potentially link to this issue for a full description).

Anyone have any other ideas?

[calebhearth]

I could swear we've seen this in an issue and documented it before. I know it but me. Best way I've found to "fix" it is drop and add the same view to pick up new columns.