Author fviard
Recipients anders.blomdell@control.lth.se, fviard, ghaering, ned.deily
Date 2011-12-23.14:05:07
SpamBayes Score 2.22045e-16
Marked as misclassified No
Message-id <1324649109.51.0.362360139629.issue10513@psf.upfronthosting.co.za>
In-reply-to
Content
Hi,

I encountered the same regression with python 2.7.
I added a new testcase (testcase2) with another effect of the same problem. It is a model of what does python-storm when I try to do:

for item in TableA.findall():
  TableB.new_item(name=item.name)
  connection.commit()

As you can see in the result:
-- One iter --
(0,)
-- One iter --
(1,)
-- One iter --
(0,)
-- One iter --
(1,)
-- One iter --
(2,)

Inside the for loop, there is a reset, but only one time. So entries returned by "for" are inconsistents as there could be some duplicated results.


After studying the code, I understood that this regression is caused by the addition of function call:
        pysqlite_do_all_statements(self, ACTION_RESET, 0);
in connection.c: in function: pysqlite_connection_commit

Removing this line, fixes the problem and bring back the old correct behavior.


For the explanation of my case, I understood that:
For the "statement" of my query, the "in_use" flag is first set to 1, the first fetchmany call ran, then the commit triggered the reset of all the statements that have  the in_use flag == 1 and set this flag to 0. So the next fetchmany call return results from the beginning again without care for the state of the "in_use" flag. Then, the next commit don't reset again the statement as its "in_use" flag is still == 0.

I think, that the first intention of this modification was more to use the following call:
pysqlite_do_all_statements(self, ACTION_RESET, 1);
to have the cursor->reset set and so, at next fetch call, triggering exception: "sqlite3.InterfaceError: Cursor needed to be reset because of commit/rollback and can no longer be fetched from."

But as for using it in rollback, I'm not sure that this behavior is really useful as sqlite3 looks to be able to correctly handle modifications and cursor return values.
As an example, the following test with the previous code :
    cursor = conn.cursor()
    cursorBis = conn.cursor()
    cursor.execute('create table first(id primary key);')
    for i in range(5):
        cursor.execute('insert or replace into first(id) values(?);', (i,))

    results = cursor.execute(' select id from first;')
    while True:
        print "-- One iter --"
        results = cursor.fetchmany()
        if not results:
            break
        for result in results:
            print str(result)
            cursorBis.execute(' delete from first where id == ?;', (3,))
        conn.commit()

That correctly printed:
-- One iter --
(0,)
-- One iter --
(1,)
-- One iter --
(2,)
-- One iter --
(4,)
-- One iter --


So my suggestion is to remove in "pysql_connection_commit" the call to :
pysqlite_do_all_statements(self, ACTION_RESET, 0);
to bring back the correct old behavior.

And also eventually to remove in "pysqlite_connection_rollback" the call to :
pysqlite_do_all_statements(self, ACTION_RESET, 1);


What do you think about it?
History
Date User Action Args
2011-12-23 14:05:09fviardsetrecipients: + fviard, ghaering, ned.deily, anders.blomdell@control.lth.se
2011-12-23 14:05:09fviardsetmessageid: <1324649109.51.0.362360139629.issue10513@psf.upfronthosting.co.za>
2011-12-23 14:05:08fviardlinkissue10513 messages
2011-12-23 14:05:08fviardcreate