Cached query plans become invalid after migration changes column datatype #1892

Closed
jaredbeck opened this Issue Jun 29, 2011 · 13 comments

8 participants

@jaredbeck
# Cause a query plan to be created
Thing.find(1)

# Change column datatype (in this case, reduce the max. varchar length)
change_column :things, :name, :string, :limit => 10 

# Try to reuse the same query plan
Thing.find(1) 

# => ActiveRecord::StatementInvalid: PGError: ERROR: cached plan must not change result type

The workaround is easy. One can simply restart one's application after such a migration. Remembering to do so is the hard part :)

Would it be possible for such a migration to automatically clear cached query plans?

Are cached query plans a new feature in Rails? Sorry, I can't say for sure which versions of rails this issue affects.

@franckverrot

Prepared statements are new yes. :)

@gucki

I think the patch provided is insufficient (it fixes the problem only with pg, not with other adapters). But maybe other adaptersdon't have bug or simply dont raise? Otherwise the reset code should be in the migration code, no the adapter code.
Anway, I'm +1 on getting this fixed :)

@franckverrot
@franckverrot franckverrot added a commit to franckverrot/rails that referenced this issue Jul 11, 2011
@franckverrot franckverrot Clear query cache after a migration [Closes #1892]
Some methods like PREPARE and DEALLOCATE have also been extracted to introduce reset_cache!.

Changes:
- Test that changing a column definition doesnt break the prepared statements.
- Extracted prepare_statement! and deallocate_statement./sbt Created reset_cache! that should be called when a DDL statement is sent to the database
- Use reset_cache! on rename_column
8e503c9
@joevandyk

Hm, I think this can't just happen on change_column. The error I had tonight was when I added a column to the table.

Also, little confused about how this works -- prepared statements are session-specific. How would resetting it in the migration process affect the other running Rails processes?

@franckverrot

It won't affect the other processes (see the test case in the patch).

@tenderlove
Ruby on Rails member

Fixed in 818d285

@tenderlove tenderlove closed this Oct 19, 2011
@joevandyk

I don't think this will work inside a transaction/migration -- failing test case here: joevandyk@718ba59

@joevandyk

@tenderlove Here is the test case (slightly changed) from this ticket, it fails on master. joevandyk@2c1612d

I don't think this ticket should be closed.

@tenderlove
Ruby on Rails member

Hi @joevandyk, I've added your test case and made them pass. Thanks! :-)

@poloten4ik

big thanks, bro!

@BBuchholz

Thanks for that, new to Heroku and this was puzzling me. Totally fixed my issue.

@mockdeep

We just saw this issue when deploying a migration to production. We added a column to the table, and a request that was in progress threw:

ActiveRecord::StatementInvalid: PG::FeatureNotSupported: ERROR: cached plan must not change result type : SELECT "forms".* FROM "forms" WHERE "forms"."id" = $1 LIMIT 1
@mockdeep

nm, there's a more recent issue: #12330

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment