René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Sqlite cursor garbage-collection issue with Python 3.11.0b3","articleBody":"**Bug report**\r\n\r\nThis was reported to me on https://github.com/coleifer/peewee/issues/2580 and, as I've managed to reproduce the issue, was asked by @erlend-aasland to submit a ticket here. Note that this issue does **not** manifest on other versions of Python (2.7, 3.6, 3.9 and 3.10) -- it appears to be a new issue on 3.11.0b3\r\n\r\n**The original reporter bisected and found the following commit introduced the regression**: https://github.com/python/cpython/commit/3df0fc89bc2714f5ef03e36a926bc795dcd5e05a\r\n\r\nI believe this is a cursor garbage collection issue, though I may be mistaken. Keeping the cursors around in memory seems to cause the problem to manifest, but if you comment-out the line indicated below then the tests will pass. Note that Peewee doesn't do anything weird like keep the cursors in memory, this was just the first way I've been able to successfully reproduce the issue.\r\n\r\nThe test below does the following:\r\n\r\n* create a 1st connection to a sqlite db and put some rows into it, committing changes\r\n* create a 2nd connection to that db and verify we can see the rows, then delete them, committing changes\r\n* begin a transaction in 1st connection, inserting 2 new rows\r\n* verify the 2nd connection cannot see the uncommitted rows (this is where the failure occurs)\r\n* verify the 1st connection can see its uncommitted rows, then commit\r\n* lastly verify the 2nd connection can see the now-committed rows\r\n\r\nWhat is doubly-confusing about this error is that, when the failure occurs, the SQL being executed should definitely NOT be returning `None` from the call to `fetchone()`. If the table did not exist, we would get a different error from Sqlite. If the table does exist and we just can't see any rows, then we should be getting 0 as the test asserts. Instead, the `fetchone()` is returning `None`.\r\n\r\nReproduce:\r\n\r\n```python\r\nimport glob\r\nimport os\r\nimport sqlite3\r\n\r\nfilename = '/tmp/test.db'\r\nfor f in glob.glob(filename + '*'):\r\n    os.unlink(f)  # Cleanup anything from prev run(s).\r\n\r\nCURSORS = {}\r\n\r\ndef sql(conn, sql, *params):\r\n    curs = conn.cursor()\r\n    curs.execute(sql, params)\r\n    CURSORS[id(sql)] = curs  # COMMENT THIS OUT AND TEST WILL PASS.\r\n    return curs\r\n\r\n# Set up database w/some sample rows. Peewee sets isolation-level to None as we\r\n# want to manage all transaction state ourselves, rather than use sqlite3's\r\n# somewhat unusual semantics.\r\ndb = sqlite3.connect(filename, isolation_level=None)\r\ndb.execute('create table users (id integer not null primary key, '\r\n           'username text not null)')\r\nsql(db, 'insert into users (username) values (?), (?), (?)', 'u1', 'u2', 'u3')\r\ndb.commit()\r\n\r\n# On 2nd connection verify rows are visible, then delete them.\r\nnew_db2 = sqlite3.connect(filename, isolation_level=None)\r\nassert sql(new_db2, 'select count(*) from users').fetchone()[0] == 3\r\nassert sql(new_db2, 'delete from users').rowcount == 3\r\nnew_db2.commit()\r\n\r\n# Back in original connection, create 2 new users.\r\nsql(db, 'begin')\r\nsql(db, 'insert into users (username) values (?)', 'u4')\r\nsql(db, 'insert into users (username) values (?)', 'u5')\r\n\r\n# 2nd connection cannot see uncommitted changes.\r\n# NOTE: this is the line that fails.\r\nassert sql(new_db2, 'select count(*) from users').fetchone()[0] == 0\r\n\r\n# Original conn can see its own changes.\r\nassert sql(db, 'select count(*) from users').fetchone()[0] == 2\r\ndb.commit()\r\n\r\n# Now the 2nd conn can see the changes.\r\nassert sql(new_db2, 'select count(*) from users').fetchone()[0] == 2\r\n```\r\n\r\nOn 3.11.0b3 the above fails on the indicated line with the following exception:\r\n\r\n```\r\nTraceback (most recent call last):\r\n  File \"/home/charles/tmp/py311/repro.py\", line 40, in \u003cmodule\u003e\r\n    assert sql(new_db2, 'select count(*) from users').fetchone()[0] == 0\r\n           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^\r\nTypeError: 'NoneType' object is not subscriptable\r\n```\r\n\r\n**Your environment**\r\n\r\n- CPython versions tested on: 2.7, 3.6, 3.9, 3.10 (passing), and 3.11.0b3 (**failing**)\r\n- Sqlite3 version 3.35 and 3.38\r\n- Linux / Debian stable","author":{"url":"https://github.com/coleifer","@type":"Person","name":"coleifer"},"datePublished":"2022-06-20T13:54:33.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":4},"url":"https://github.com/94028/cpython/issues/94028"}