SQLAlchemy

Email Archive: sqlalchemy-tickets (read-only)

Re: [Sqlalchemy-tickets] [sqlalchemy] #2203: 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

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 05:31

#2203: 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
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek               
        Type:  defect            |      Status:  closed               
    Priority:  medium            |   Milestone:  0.7.2                
   Component:  documentation     |    Severity:  trivial - <10 minutes
  Resolution:  fixed             |    Keywords:                       
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------

Comment(by guest):

 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!

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:10>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Thread View

Thread	Author	Date
[Sqlalchemy-tickets] [sqlalchemy] #2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7	sqlalchemy <mike_mp@zz...>

Re: [Sqlalchemy-tickets] [sqlalchemy] #2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7

From: sqlalchemy <mike_mp@zz...> - 2011-06-28 14:23

#2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek                
        Type:  defect            |      Status:  closed                
    Priority:  medium            |   Milestone:                        
   Component:  sqlite            |    Severity:  no triage selected yet
  Resolution:  worksforme        |    Keywords:                        
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------
Changes (by zzzeek):

  * status:  new => closed
  * resolution:  => worksforme
  * status_field:  awaiting triage => completed/closed


Comment:

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

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7

From: sqlalchemy <mike_mp@zz...> - 2011-06-28 14:24

#2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek                
        Type:  defect            |      Status:  closed                
    Priority:  medium            |   Milestone:                        
   Component:  sqlite            |    Severity:  no triage selected yet
  Resolution:  worksforme        |    Keywords:                        
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------

Comment(by zzzeek):

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

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 00:44

#2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek                
        Type:  defect            |      Status:  reopened              
    Priority:  medium            |   Milestone:                        
   Component:  sqlite            |    Severity:  no triage selected yet
  Resolution:                    |    Keywords:                        
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------
Changes (by guest):

  * status:  closed => reopened
  * resolution:  worksforme =>


Comment:

 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)

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 00:45

#2203: temporary tables disappear on commit in sqlite + sqlalchemy 0.7
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek                
        Type:  defect            |      Status:  reopened              
    Priority:  medium            |   Milestone:                        
   Component:  sqlite            |    Severity:  no triage selected yet
  Resolution:                    |    Keywords:                        
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------

Comment(by guest):

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

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 04:51

#2203: 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
------------------------------+---------------------------------------------
    Reporter:  guest          |       Owner:  zzzeek               
        Type:  defect         |      Status:  reopened             
    Priority:  medium         |   Milestone:  0.7.2                
   Component:  documentation  |    Severity:  trivial - <10 minutes
  Resolution:                 |    Keywords:                       
Status_field:  in progress    |  
------------------------------+---------------------------------------------
Changes (by zzzeek):

  * status_field:  completed/closed => in progress
  * component:  sqlite => documentation
  * severity:  no triage selected yet => trivial - <10 minutes
  * milestone:  => 0.7.2


Comment:

 my apologies.

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:5>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 04:54

#2203: 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
------------------------------+---------------------------------------------
    Reporter:  guest          |       Owner:  zzzeek               
        Type:  defect         |      Status:  reopened             
    Priority:  medium         |   Milestone:  0.7.2                
   Component:  documentation  |    Severity:  trivial - <10 minutes
  Resolution:                 |    Keywords:                       
Status_field:  in progress    |  
------------------------------+---------------------------------------------

Comment(by zzzeek):

 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 ?

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:6>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 04:59

#2203: 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
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek               
        Type:  defect            |      Status:  closed               
    Priority:  medium            |   Milestone:  0.7.2                
   Component:  documentation     |    Severity:  trivial - <10 minutes
  Resolution:  fixed             |    Keywords:                       
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------
Changes (by zzzeek):

  * status:  reopened => closed
  * resolution:  => fixed
  * status_field:  in progress => completed/closed


Comment:

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

 this is in r5133ccc75bd5.

 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.

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:7>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 05:05

#2203: 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
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek               
        Type:  defect            |      Status:  reopened             
    Priority:  medium            |   Milestone:  0.7.2                
   Component:  documentation     |    Severity:  trivial - <10 minutes
  Resolution:                    |    Keywords:                       
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------
Changes (by zzzeek):

  * status:  closed => reopened
  * resolution:  fixed =>


Comment:

 forgot the wiki

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:8>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 05:06

#2203: 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
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek               
        Type:  defect            |      Status:  closed               
    Priority:  medium            |   Milestone:  0.7.2                
   Component:  documentation     |    Severity:  trivial - <10 minutes
  Resolution:  fixed             |    Keywords:                       
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------
Changes (by zzzeek):

  * status:  reopened => closed
  * resolution:  => fixed


Comment:

 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.

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:9>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 05:31

#2203: 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
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek               
        Type:  defect            |      Status:  closed               
    Priority:  medium            |   Milestone:  0.7.2                
   Component:  documentation     |    Severity:  trivial - <10 minutes
  Resolution:  fixed             |    Keywords:                       
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------

Comment(by guest):

 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!

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:10>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

From: sqlalchemy <mike_mp@zz...> - 2011-06-29 05:48

#2203: 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
---------------------------------+------------------------------------------
    Reporter:  guest             |       Owner:  zzzeek               
        Type:  defect            |      Status:  closed               
    Priority:  medium            |   Milestone:  0.7.2                
   Component:  documentation     |    Severity:  trivial - <10 minutes
  Resolution:  fixed             |    Keywords:                       
Status_field:  completed/closed  |  
---------------------------------+------------------------------------------

Comment(by zzzeek):

 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.

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2203#comment:11>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python