Add setting for enumerating column names in SELECT statements by dzunk · Pull Request #41718 · rails/rails

dzunk · 2021-03-21T22:58:06Z

Summary

Add a new configuration setting ActiveRecord::Base.enumerate_columns_in_select_statements. When truthy, ActiveRecord will explicitly project all column names in SELECT statements, rather than a wildcard table_name.*.

E.g. SELECT * FROM posts becomes SELECT id, title, body, created_at, updated_at FROM posts

Other Information

When a column is added to a Postgres table, it changes the query result for any wildcard SELECTs. This causes PreparedStatementCacheExpired errors when the statement occurs inside a transaction. #22170 cleans up the statement cache, but the error is still raised and will fail the transaction, and probably cause a 500 error, failed background job, etc.

I've seen a few different recommendations on how to handle these:

The last option above works because setting any value for ignored_columns triggers the code path to project all column names instead of a wildcard, and this is what we're currently using in production.

# app/models/application_record.rb
class ApplicationRecord
  self.ignored_columns = [:__fake_column__]
end

However, this feels hacky, and isn't how ignored_columns is supposed to be used. Adding an option to enable this behavior all the time seems cleaner, and makes it easier to opt-in on a per-model, or per-environment basis.

# config/application.rb
module MyApp
  class Application < Rails::Application
    config.active_record.enumerate_columns_in_select_statements = true
  end
end

If the code looks good and is agreeable to everyone, I'll make sure to update the docs and add a changelog entry as well.

activerecord/lib/active_record/core.rb

rafaelfranca · 2021-03-24T05:13:09Z

This makes sense to me. Just a small comment that we should use class_attribute.

uberllama · 2021-04-20T15:43:04Z

This is a brilliant addition to finally and cleanly deal with a real world pain, which anyone working on a highly transactional system deals with regularly. Cheers. 👍

elspethsoup · 2021-10-13T21:47:15Z

It would be awesome if this got back ported to rails 6.1.

santib · 2022-05-05T20:34:01Z

guides/source/configuring.md


 * `config.active_record.queues.destroy` allows specifying the Active Job queue to use for destroy jobs. When this option is `nil`, purge jobs are sent to the default Active Job queue (see `config.active_job.default_queue_name`). It defaults to `nil`.

+* `config.active_record.enumerate_columns_in_select_statements` when truthy, will always include column names in `SELECT` statements, and avoid wildcard `SELECT * FROM ...` queries. This avoids prepared statement cache errors when adding columns to a Postgres database. Defaults to `false`.


Just curious, but why does it default to false? Is there any drawback to enumerating the columns instead of using *?

As far as I know there's no drawback, but it's off by default to guarantee backwards compatibility by maintaining existing behavior.

One small drawback is that the query logs will be bigger/more verbose when showing the SQL generated by ActiveRecord.

atagunay

thanks man !!!

danielvdao · 2023-06-07T16:52:43Z

I took a look at the Rails backporting policy and would be curious to know if this feature could be backported to Rails 6 🙇🏽‍♂️

Generally, security fixes and bug fixes are good candidates for a backport, while new features and patches that introduce a change in behavior will not be accepted. When in doubt, it is best to consult a Rails team member before backporting your changes to avoid wasted effort.

While this seems like a feature, I feel like this is a bug, considering that it's taken down high traffic Rails applications. For us it's shot developers in the foot when running migrations that touch a table with a query in a transaction. As a result we've had to do multiple force deploys, but that seems like a workaround as opposed to this PR.

uberllama · 2023-06-07T17:41:36Z

I took a look at the Rails backporting policy and would be curious to know if this feature could be backported to Rails 6 🙇🏽‍♂️

Generally, security fixes and bug fixes are good candidates for a backport, while new features and patches that introduce a change in behavior will not be accepted. When in doubt, it is best to consult a Rails team member before backporting your changes to avoid wasted effort.

While this seems like a feature, I feel like this is a bug, considering that it's taken down high traffic Rails applications. For us it's shot developers in the foot when running migrations that touch a table with a query in a transaction. As a result we've had to do multiple force deploys, but that seems like a workaround as opposed to this PR.

It's a one line change in ApplicationRecord as per the first post to implement this functionality. The new config entry has just made that idiomatic.

rails-bot bot added the activerecord label Mar 21, 2021

rafaelfranca reviewed Mar 24, 2021

View reviewed changes

activerecord/lib/active_record/core.rb Outdated Show resolved Hide resolved

rails-bot bot added the docs label Mar 28, 2021

dzunk force-pushed the enumerate_columns branch 4 times, most recently from 7cfeef4 to 28db742 Compare March 31, 2021 15:13

dzunk force-pushed the enumerate_columns branch from 28db742 to b3ea8aa Compare April 13, 2021 17:50

dzunk requested a review from rafaelfranca April 14, 2021 18:42

Add setting for enumerating column names in SELECT statements

726abea

dzunk force-pushed the enumerate_columns branch from b3ea8aa to 726abea Compare April 14, 2021 22:48

rafaelfranca merged commit 7ce853e into rails:main Apr 15, 2021

rafaelfranca added a commit that referenced this pull request Apr 15, 2021

Copy edit #41718

7b198e6

dzunk deleted the enumerate_columns branch April 16, 2021 04:33

benoittgt mentioned this pull request Apr 28, 2021

add_column should raise warning ankane/strong_migrations#85

Closed

Kevinrob mentioned this pull request Jun 7, 2021

Add a configuration explicit_select_column_names for fixing PreparedStatementCacheExpired errors when adding new columns #42412

Closed

santib reviewed May 5, 2022

View reviewed changes

atagunay reviewed Dec 12, 2022

View reviewed changes

Nitrodist mentioned this pull request Feb 22, 2023

PostgreSQL prepared statement failure inside transaction results in a permanently cached prepared statement that's never cleaned up. #12330

Closed

kamipo mentioned this pull request Jan 14, 2024

ActiveRecord#attributes is affected immediately after migration #50748

Closed

This was referenced Apr 4, 2025

Prevent ActiveRecord errors when adding columns alphagov/forms-admin#1877

Merged

Prevent ActiveRecord errors when adding columns alphagov/forms-api#725

Merged

Prevent ActiveRecord errors when adding columns alphagov/forms-runner#1312

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add setting for enumerating column names in SELECT statements#41718

Add setting for enumerating column names in SELECT statements#41718
rafaelfranca merged 1 commit intorails:mainfrom
dzunk:enumerate_columns

dzunk commented Mar 21, 2021 •

edited

Loading

Uh oh!

Uh oh!

rafaelfranca commented Mar 24, 2021

Uh oh!

uberllama commented Apr 20, 2021

Uh oh!

elspethsoup commented Oct 13, 2021

Uh oh!

santib May 5, 2022

Uh oh!

dzunk May 5, 2022

Uh oh!

swanson Aug 26, 2022

Uh oh!

atagunay left a comment

Uh oh!

danielvdao commented Jun 7, 2023 •

edited

Loading

Uh oh!

uberllama commented Jun 7, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants


		* `config.active_record.queues.destroy` allows specifying the Active Job queue to use for destroy jobs. When this option is `nil`, purge jobs are sent to the default Active Job queue (see `config.active_job.default_queue_name`). It defaults to `nil`.

		* `config.active_record.enumerate_columns_in_select_statements` when truthy, will always include column names in `SELECT` statements, and avoid wildcard `SELECT * FROM ...` queries. This avoids prepared statement cache errors when adding columns to a Postgres database. Defaults to `false`.

Conversation

dzunk commented Mar 21, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Other Information

Uh oh!

Uh oh!

rafaelfranca commented Mar 24, 2021

Uh oh!

uberllama commented Apr 20, 2021

Uh oh!

elspethsoup commented Oct 13, 2021

Uh oh!

santib May 5, 2022

Choose a reason for hiding this comment

Uh oh!

dzunk May 5, 2022

Choose a reason for hiding this comment

Uh oh!

swanson Aug 26, 2022

Choose a reason for hiding this comment

Uh oh!

atagunay left a comment

Choose a reason for hiding this comment

Uh oh!

danielvdao commented Jun 7, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

uberllama commented Jun 7, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants

dzunk commented Mar 21, 2021 •

edited

Loading

danielvdao commented Jun 7, 2023 •

edited

Loading