(This is mainly not a question, but a tip. But digging deeper this is still a question to sqlite-zstd developer.)
I'm using Python with SQLAlchemy (SQLModel as the helper library) with sqlite-zstd. I encountered this error when trying to update a record after enabling sqlite-zstd compression on my table:
StaleDataError: UPDATE statement on table 'queryrecord' expected to update 1 row(s); 0 were matched.
It turns out to be indeed related to sqlite-zstd: if compression is enabled and trying to update a row, SQLAlchemy will fail to verify the number of modified rows (c.f. [1]). In fact, this is mentioned (though not appearing to be related at a first glance) in the README:
sqlite3_changes() will return 0 for modifying queries (see here).
I found a solution: turn off this feature of SQLAlchemy ([ref]):
Hope this helps others like me, who are not experienced sqlite or sqlalchemy developers/users but wanting to do something little but helps reducing size of database.
In the meantime, I'd be curious to know why must sqlite3_changes() return 0? Is it a temporary trick, or is there some deep reason?
(Feel free to close this issue if that's more appropriate.)
(This is mainly not a question, but a tip. But digging deeper this is still a question to sqlite-zstd developer.)
I'm using Python with SQLAlchemy (SQLModel as the helper library) with sqlite-zstd. I encountered this error when trying to update a record after enabling sqlite-zstd compression on my table:
It turns out to be indeed related to sqlite-zstd: if compression is enabled and trying to update a row, SQLAlchemy will fail to verify the number of modified rows (c.f. [1]). In fact, this is mentioned (though not appearing to be related at a first glance) in the README:
I found a solution: turn off this feature of SQLAlchemy ([ref]):
Hope this helps others like me, who are not experienced sqlite or sqlalchemy developers/users but wanting to do something little but helps reducing size of database.
In the meantime, I'd be curious to know why must
sqlite3_changes()return 0? Is it a temporary trick, or is there some deep reason?(Feel free to close this issue if that's more appropriate.)