CODEWITHSUNDEEP
pythondatabasecachingvisual-studio-codesqlalchemy
Jona
Jona

Reputation: 21

SQLAlchemy Repeated Commit()

I've run into a really frustrating bug, but I'm not sure exactly how to phrase my question. The core behavior seems to be as follows:

1) Create a new db.session, bound to an existing PostgreSQL database
2) Run db.session.add(myObj)
3) Run db.session.commit()
>>> Check the database using PGAdmin, myObj was successfully uploaded
4) *
5) Run db.session.query(myClass) as many times as I want
>>> Returns [myObj]
6) Run db.session.query(myClass).filter(anyFilterThatDoesNotActuallyChangeResult)
>>> Returns [myObj]
>>> BUG >>> 5 seconds later, another copy of myObj is added to the database (visible in PGAdmin)
7) Repeat step 6 as many times as you want
>>> Returns [myObj, myObj]
8) Repeat step 5
>>> Returns [myObj, myObj]
>>> BUG >>> 5 seconds later, another copy of myObj is added to the database (visible in PGAdmin)

Further confusing information: I can completely close and restart my text editor and python environment at step 4, and the buggy behavior persists.

My intuition is that the COMMIT string is somehow being cached somewhere (in SQLAlchemy or in PostgreSQL) and whenever the query command is changed, that triggers some sort of autoflush on the DB, thereby rerunning the commit string, but not actually clearing that cache upon success.

----------------- EDIT -----------------

IGNORE THE REST OF THIS QUESTION, AS IT WAS NOT RELEVANT TO THE BUG AT HAND.

To further explore this behavior, I ran the following code:

1) Create a new db.session, bound to an existing PostgreSQL database
2) Run db.session.add(myObj)
3) Run db.session.commit()
4) Run db.session.commit()

Which, I would expect to only add ONE copy of myObj, but instead it actually adds TWO!!! This breaks my understanding of what commit is doing--specifically autoflushing, ending the transaction, and removing add(myObj) from its "to do" list. Furthermore, none of the code I try running between lines 3 and 4 will prevent this behavior: for example db.session.expire_all()

I am a complete noob around databases (this is my first project), so I would appreciate any suggestions, especially explicit step-by-step recommendations for how I can overcome this bug. E.g. What code should I add in, and where, to clear such a cache?

Upvotes: 0

Views: 376

Answers (1)

Jona
Jona

Reputation: 21

Turns out that the problem was more nefarious than I imagined. The steps to repeat were actually more basic than that:

1) Save any file in the same directory as my session manager
>>> BUG >>> 15 seconds later another copy of myObj is added to the database

I am using VS Code (Version: 1.47.3), and the bug only happens while the Python extension is enabled.

My running hypothesis is that because one of the files in the directory auto-initializes a database session (via pyscopg2), there is some caching mechanism that executes that code in a poorly-managed state, which somehow successfully manages to establish a new engine connection, followed by whatever the last commit statement was.

I have stopped trying to debug it, and moved to a refactor of the session management structure so that the connection is only established within a function call, as opposed to whenever the file is run.

Thanks for reading. Hope this helps someone else hitting this infuriatingly unreproducible bug. Literally thought I was going crazy: each time I absent-mindedly saved my file, a mystery object would appear. I would save at different points and at different frequencies, so the behavior appeared utterly random. The only reason I found the original steps to reproduce was because the debugger I was using saved the file before running it.

----------------- FINAL SOLUTION -----------------

It turns out the root of all my woes was my choice of names. I had written some code that tested my sql code, but foolishly named it test_XXX.py Then, whenever any file was saved, pytest would do an automatic sweep of all the files that started test_* and run them, thus causing my entire SQL example work to be run behind the scenes.

Tune in next week for more adventures in Things That I Could Have Prevented.

Upvotes: 2

Related Questions