Pro: less code, less complexity, improved maintainability
Con: minor performance hit

PoC here: https://github.com/erlend-aasland/cpython/commits/sqlite-cache

$ ./python.exe
>>> import sqlite3
>>> con = sqlite3.connect(":memory:")
>>> con.execute("select * from sqlite_master")
>>> con.execute("select * from sqlite_master")
>>> c = con.cache()
>>> c.cache_info()
CacheInfo(hits=1, misses=1, maxsize=128, currsize=1)

The test suite runs approx. 10-20 ms slower with this change. Using _functools._lru_cache_wrapper iso. functools.lru_cache almost removes this performance regression.

Berker, is it worth pursuing?

Short diffstat: 8 files changed, 85 insertions(+), 406 deletions(-)

+1 This seems reasonable to me.

I can throw up the PoC branch as a draft PR after #68323 is merged. We can just close the PR if this is uninteresting or something we want to postpone.

I don't see any reason to merge #68323 if we are going to remove cache.[ch] in this issue.

I was -0 before but since Raymond gave his +1, you can count me as +1 too.

I don't see any reason to merge #68323 if we are going to remove cache.[ch] in this issue.

Yes, I've thought about that myself. A small argument pro merging #68323 would be that if we for some reason decide to revert this change, then cache.[ch] would still be prepared for module state (which is needed for multi-phase init).

We can always reopen #68323 and merge it even if we revert this one for some reason :)

True that :) I'll close #68323 for now and open a PR for this later today.

I do not like using _functools._lru_cache_wrapper. It is a deep implementation detail, private function of private module. Use functools.lru_cache. If it is few nanoseconds slower, that cost is only added at connection creation time. It is insignificant in any real application in comparison with IO and any real work with data. Thousands of short-living in-memory DB instances are only created in tests.

I do not like using _functools._lru_cache_wrapper. It is a deep implementation detail, private function of private module. Use functools.lru_cache.

All right, thanks.

I need some help debugging the Windows issues. There are a handful of tests that fail because the sqlite3 is clinging on to objects or file descriptors. AFAICT, a GC bug. For example, test_open_uri fails with:

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: [...]

I've tried the following, with no success:

• Remove GC from the Connection type
• Remove the clear method, and modify traverse to only visit Py_TYPE(self) (https://bugs.python.org/issue42866#msg384675)
• Add heap type GC (visit type, GC tracking) to all pysqlite types

Maybe it's best to reopen #68323 and continue the work with multi-phase init? That is finalise bpo-40956, add heap type GC (https://bugs.python.org/issue42866#msg384675), establish module state, implement multi-phase init, and then revisit this issue.

Tried applying bpo-42972 to sqlite and functools, but the error persists.

This works:

1. fully implement GC in connection (bpo-42972)
2. also visit statement_cache
3. explicitly close connections _and_ call GC in problematic tests

The first point might not be needed for this particular fix.
The last point is a workaround, not a solution. Or is it ok to call gc.collect() in the test suite?

Or is it ok to call gc.collect() in the test suite?

Seems like it's ok:

$ grep -r gc.collect Lib/test | wc -l
    366

Or is it ok to call gc.collect() in the test suite?

It is but in this case I'd say it's a bit weird that we need to call it. Unfortunately, I don't have much time to investigate it at the moment.

New changeset f461a7f by Erlend Egeberg Aasland in branch 'main':
bpo-42862: Use functools.lru_cache iso. _sqlite.Cache in sqlite3 module (GH-24203)
f461a7f

New changeset 34356a0 by Erlend Egeberg Aasland in branch 'main':
bpo-42862: Strip stale sqlite3 cache ignores from c-analyzer (GH-26876)
34356a0