I just finished migrating my Flask app from Postgres to DuckDB, and pretty much everything seems to be working perfectly. There is one very odd issue though that I've been banging my head against the wall on for hours and can't seem to make heads or tails of it.
In one of my Flask routes there are various things that happen pertaining to stuff a user is uploading. Over the course of the processing the files go through the app makes updates to a few different tables in the db. Everything looks 100% normal and correct in the app, but when I quit Flask, the final three table updates simply disappear. The given Flask route is really long and complicated, but everything works perfectly as it did before up until a certain point…
** whole bunch of stuff happens above here **
try:
db.session.commit()
except Exception as e:
db.session.rollback()
db.session.add(new_audit_history_entry)
db.session.add(new_audit_log_entry)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
update_task = TaskQueue.query.get(task_id)
update_task.completed_at = datetime.now()
update_task.status = 'Complete'
try:
db.session.commit()
except Exception as e:
db.session.rollback()
finally:
audit_history = AuditHistory.query.all()
audit_logs = AuditLog.query.all()
tasks = TaskQueue.query.all()
db.session.flush()
db.session.close()
return jsonify({'message': 'Files saved and validated!', 'This many files: ': len(files)})
I'm not getting any errors whatsoever, and everything looks normal in the app — I can see everything I would expect to see. All is well. But then if I quit Flask (ctl+c in the CLI) everything from db.session.add(new_audit_history_entry) onwards is simply gone. The entries in audit_history and audit_log tables disappear, and the TaskQueue table has reverted to it's state further up in the route the last time it was updated (before the final commit to that table seen above).
The only hint I've seen is when I start Flask back up I get the following:
Exception in WAL playback: Violates foreign key constraint because key "auditid: 95693563-b229-40d7-9b12-5c4447bdb601" does not exist in the referenced table
So it seems that those final commits are not being fully saved? They're only being saved in Duck's write ahead logic or something? I've tried every possible combination of commits and flushes to force these final updates to the db and no dice.
To be clear I'm not interrupting the app in the middle of writes or anything, I'm quitting and relaunching it way after these writes would have finished. Like I'm convinced that I could run the app for a day and none of these particular writes would persist after quitting. Also, I am handling the keyboard interrupt elegantly in my app, including a final db commit for good measure.
What am I missing? Is there something special about duck that I need to do?
Good lord, finally figured it out. It turns out that .commit() and .close() do not automatically trigger synchronization of the WAL file with the static DB file.
You need to invoke CHECKPOINT (docs here) to force a sync. The Python API does not have anything built in for this, so you need to execute it as SQL. In my case, using SQLalchemy I added db.engine.execute('CHECKPOINT;') to my "graceful exit" function:
def handler(signal_received, frame):
with app.app_context():
print('SIGINT or CTRL-C detected. Gracefully exiting')
# Force a checkpoint in DuckDB to synchronize WAL with the main database
try:
db.engine.execute('CHECKPOINT;')
print("Database CHECKPOINT completed.")
except Exception as e:
print(f"Failed to execute CHECKPOINT: {e}")
db.session.commit()
db.session.close()
exit(0)
And that worked for me.