Berker:
> My understanding is that their usages is a bit different:
> one is for performance reasons (and it can configurable by users) and the other is to keep track of statements inside a connection.
>
> I'm not fully sure that the both use cases can be combined.

Only unique statements are added to the weak ref list (and the LRU cache).

Case 1: len(unique statements) <= 128
Both the LRU cache and the weak ref list will be equal sets of statements.

Case 2: len(unique statements) > 128 <= 200
If more than 128 unique statements are created, they "fall off" of the LRU cache and are collected by the GC. The weak ref list will contain all the 128 cached statements, and up to 72 "lost" statements which have been reaped by the GC.
The LRU cache and the _alive_ objects in weak ref list will be equal sets of statements.

Case 3: len(unique statements) > 200
_pysqlite_drop_unused_statement_references() will remove the lost 72 refs and create a new weak ref list that will be equal to the 128 statements in the LRU cache.

Fun fact: After the number of unique statements pass 200, the weak ref list is reset to the remaining 128 "active" statements, and sqlite3.Connection.created_statements is reset to 0. Thus, when we pass the "200 limit" again, the weak ref list will contain 329 elements, not 200 elements.

Any statement that "falls off" of the LRU cache is reaped by the GC which implies that sqlite3_finalize() is called on it, and it's thus removed from the SQLite connection. This implies that the set of statements available through sqlite3_next_stmt() is equal to the set of statements in the LRU cache and the set of _alive_ statements in the weak ref list.

This implies that if we want to finalise all connection statements in sqlite3.Connection.close(), all we need to do is to clear the LRU cache; the GC will invoke stmt_dealloc(), which will finalise the SQLite statement objects. If we want to reset all connection statements (pysqlite_do_all_statements()), we can iterate through them using sqlite3_next_stmt().

However, there is a corner case for when the sqlite3.Connection.statements list can contain statements that are not in the LRU cache: if we've got more than 128 active cursors, all of them with unique statements, we can end up with statements that are _only_ owned by a cursor. However, sqlite3_next_stmt() will still find them if we need to reset them in pysqlite_do_all_statements(). On connection close, all cursors are destroyed anyway, so we will end up finalising all statements, weak ref list or not.

Can we merge PR-25998 now?

Prove me wrong :)