Re: [pysqlite] unusual memory behavior

Michael Bayer wrote:
> This is not necessarily a "bug", but this behavior became apparent  
> through the SQLAlchemy unit tests.  We have a series of tests that  
> attempt to detect memory leaks by running a block of code repeatedly,  
> performing a gc.collect() and then comparing the count of objects in  
> gc.get_objects() over time.   We consider a memory leak to be when the  
> number of objects grows steadily for 30-40 iterations in a row with no  
> dips.
> 
> So with the latest pysqlite 2.5.5 built statically on an intel mac  
> against sqlite 3.6.14, we get a steadily growing behavior that grows  
> for about 250 iterations before dipping back down. 

It should be pretty much exactly every 200 iterations that object count
gets reduced again :-)

> Its a little strange and it means we'll eventually have to disable 
> that test for pysqlite, once the current versions find their way into
> the python distribution. The good news is that all SQLalchemy unit
> tests besides this one do pass on this pysqlite build. [...]

I looked into this and am glad that it's not a bug. Let me explain what
happens here.

Internally, pysqlite has these objects: connections, cursors and
statements. Connections need to have access to all cursors and
statements ever created on them. So, the connection object has a *list*
of weak references for cursors, and a *list* of weak references for
statements. To keep things simple and efficient, this list is only ever
appended to. The connection object has also two members
created_statements and created_cursors that counts the number of
statement and cursor allocations.

If created_statements or created_cursors reaches 200, the corresponding
list is scanned for weak references that have unreachable objects (i. e.
that were del-ed or removed by the gc). The list is then compacted to
not contain any NULLs. And the counter member is reset to zero.

The two methods are

_pysqlite_drop_unused_statement_references
and_pysqlite_drop_unused_cursor_references in src/connection.c.

-- Gerhard