Hi, I've got an issue which pops up from time to time with an SQLAlchemy based webservice application and it revolves around schema changes happening while an engine with autoload=True has been instantiated and is being used to start sessions.
What happens is that someone on the team will make a schema change (mostly backwards compatible, for example increasing the varchar size of a field) without asking for a restart of the webservice. Subsequent calls to the webservice will fail with quite obscure (i.e. seemingly unrelated) error messages. For example, I've seen errors like the following: Error: type object 'AppEnvRpe2Table' has no attribute 'env' (AppEnvRpe2Table is the object mapped to an sqlalchemy.Table.) First I thought someone had dropped the column (they'd actually do that...) but to my surprise the given table did have a column 'env' (and it did since the start). A restart of the service (i.e. reload of the schema) always fixed this kind of problem, which led me to believe it's related to the engine's internal representation of the schema somehow conflicting with updates in the database even when it's in places that aren't used in the particular query being executed. Obviously, it's never a good idea to change the schema when a process is running, but in this case I have no control over the changes being made. I've warned about it but it happens anyway and then on next execution of the webservice a crash results. Sometimes quite a bit of time between the schema change and the calling of the webservice can occur so it's not always immediately clear that it was the schema change that caused the problem. (I'm actually thinking of keeping this issue around because I've won lots of beers when they complained about crashes and it turned out to be their fault to begin with but you can drink only so much... ;) So that being the set-up, I'm wondering what I can do to remedy the situation. Things I considered: - Re-create the engine on every request. Bad, because it increases run- time for every request and most of the time (i.e. when there was no schema change) it is unnecessary. There are quite a few tables in the schema and autoload time is not negligible. - Re-create the engine after a schema change. The question is how do I programmatically notice a schema change? I can't rely on exceptions, because there's not a predictable error popping up when this occurs. - Catch errors and retry with new engine instance. The problem is that I'd have to catch pretty much any exception (unpredictable errors, see above), and I'd possibly obscure other issues by doing so. - Get an extra liver and just suffer the consequences... Any ideas greatly appreciated! PS: This is sqlalchemy 0.5.8 and I cannot easily update to 0.6 if that should be required. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
