sql: ActiveRecord / Rails ORM support

[lgo]

This is an issue tracking support for ActiveRecord and its features. Many of the extra features (partial indexes, ranges, comments) can be gated if the adapter so chooses to support them, making it easy to keep them disabled until we support them. If you have arrived here from one of the referenced issues, please comment on this issue to make others aware the change can be made to ActiveRecord.

• ALTER TABLE ... ALTER COLUMN ... TYPE <type> sql: support ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE ... #9851. This is required for connection.change_column(...) statements. This is frequently used for migrations. To mitigate the problem, we can modify the adapter to generate an alternative (new col, backfill, drop col, rename col).
• Partial indexes sql: implement partial indexes #9683. Standard feature support, gated by the adapter.
• Certain generated queries with GROUP BY are unsupported. sql: allow GROUP BY of ungrouped non-aggregate columns if the GROUP BY contains the primary key #20729. The impact of this needs to be investigated, but it may be any use of Model.group. This may be able to be resolved by modifying the adapter.
• Ranges sql: Support RANGE types #17022. Standard feature support, gated by the adapter.
• SQL comments. sql: Support 'COMMENT ON ...' for tables, columns and other objects #19472. Standard feature support, gated by the adapter.
• FOR UPDATE. sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}) #6583. This is used for Model.lock! statements.
• Savepoints. sql: support rolling back nested transactions containing schema changes #10735. This prevents explicit savepoints and many uses of transactions.
• Computed indexes. sql: support expression-based index columns #9682. Standard feature support, gated by the adapter.
• Correlated sub-queries. sql: support correlated subqueries #3288. Required for some generated queries made when retrieving associations. It may be possible to modify the adapter to work around this. See this ActiveRecord test for code that generates a correlated sub-query.
• Virtual columns (aka computed columns). sql: implement computed columns #20882. Standard feature support, gated by the adapter.

There are more issues, but they have not been tracked down to CockroachDB issues or just adapter issues. See lego/activerecord-cockroachdb-adapter/CONTRIBUTING.md for the full list.

[knz]

cc @awoods187 for tracking

[bobvawter]

RFC for changing column type in #24703.