document that SQLite cannot maintain temporary tables across more than one connection and that a special pool class must be used to maintain temporary tables across checkouts

[Former user]

SQLAlchemy changed the way temporary tables are handled due to the pool class change in 0.7, which broke my app. I don't think temporary tables were intended to be quite so temporary. :-) Attaching an example.

[Mike Bayer]

there's no bug here, simply use SingletonThreadPool or StaticPool if you need the same connection to remain persistent for all usages.

[Mike Bayer]

Also temporary tables aren't generally used across transactions in any case so the use case here is questionable.

[Former user]

I'm familiar with the workaround, and have already patched my app, but with all due respect I think you have closed this prematurely. Temporary tables are persistent per session (not per transaction) in sqlite, postgres and possibly other databases, and not to mention previous versions of SQLAlchemy. Given SQLAlchemy has deviated from the way (py)sqlite connections temp tables, I think at the very least this needs to be documented in the 0.7 release notes and SQLAlchemy's sqlite documentation so other people don't have to dig around to find out why their tables are disappearing.

(do I need to reopen for you to get replies? if not, sorry)

[Former user]

Sorry, that should have read "the way (py)sqlite connections handle temp tables"

[Mike Bayer]

my apologies.

[Mike Bayer]

There is really nothing at all SQLAlchemy can do about this severe limitation of pysqlite. If Pysqlite blows away temp tables when a connection completes, that's a failure on Pysqlite's part IMHO - the NullPool approach is far more efficient and scalable for general use.

Have you considered opening a bug with the Pysqlite/sqlite3 trackers ?

[Mike Bayer]

here's the new documentation section: http://www.sqlalchemy.org/docs/dialects/sqlite.html#using-temporary-tables-with-sqlite

this is in 57b91143ef74f0efb2c9b9b6caa858db7c0de2d5.

Closing this as its not worth undoing the NullPool default, which I think is a better general use default, and the caveat is now documented. If you have any other ideas, feel free to reopen.

[Mike Bayer]

forgot the wiki

[Mike Bayer]

The wiki section on the SQLite change at http://www.sqlalchemy.org/trac/wiki/07Migration#SQLite-theSQLitedialectnowusesNullPoolforfile-baseddatabases is now moved to the "backwards incompatible changes" section and the aforementioned documentation section is referred to.

[Former user]

Thanks for updating the docs. I don't think this so much a bug in sqlite as intended behaviour - temp tables are specific to a connection and are lost when the connection is closed. I can see how NullPool could be a more useful default in the common case, and the number of affected people may be low as you say. I'm mildly curious about the performance implications of an implicit close on commit, as it means sqlite is throwing away its page cache and pysqlite loses its statement cache, but the effect is probably not that great. My primary concern was that the behaviour was not documented, and that's no longer the case. Thanks again!

[Mike Bayer]

I was having problems with locks and such and in my performance testing found that NullPool made on of my apps faster due to increased concurrency.

[Mike Bayer]

Removing milestone: 0.7.2 (automated comment)