PostgreSQL support

[gstavrinos]

Hello Bartosz and Michal,

First of all, thank you for your contribution in the ROS community with this package. Impressive work!

We (https://github.com/aperion-robotics) are currently working on adding support for PostgreSQL as a fault storage database. The fork should be visible in the upcoming days.

Unfortunately, sqlite could not satisfy our needs. Our main goal is to make the integration easier with the rest of our infrastructure, and simplify the local to online/remote database offloading.

I am reaching out due to the rapid pace of development of this repository and the potential changes that occur due to newfound issues.

To be more specific, we had a complete PostgreSQL implementation about a week ago, and then noticed the large amount of changes that were introduced with the fix for multiple rosbags support per fault. Currently we are, once more, working on integrating PostgreSQL with the new database schema and filesystem handling.

Our main concern is having the same "breaking" changes happen before our integration is complete. It is very important that all your changes are backwards compatible and we respect that. For a new integration though, all these new changes are non-applicable, and mainly just introduce extra work to check already implemented functions or create new ones.

We are currently cleaning up functions (like the "old" way to check fault-rosbag referencing). Also new code should be implemented to generate the new schema right from the start and support the new functionalities.

At this point, it is very important for us to hear your thoughts on some matters.

Is PostgreSQL something that you have considered in the past? Do you have a roadmap regarding its integration? Do you see any drawbacks? Given the fact that we plan to open a PR soon, would it be something you would be interested in supporting in the future?

Additionally regarding our next steps, do you think that the codebase will be stable enough in the upcoming days in order for us to have the time to make a PR with the new implementation? (Without the need to re-visit our implemented code to integrate newer changes)

Sorry for the very long issue. Eager to hear your thoughts.

George

[bburda]

Hi George! I'm really sorry for the churn on the schema. Multiple rosbags per fault was necessary because a fault usually happens more than once in a mission, in different places and under different conditions, and we want all of those occurrences under one fault entry with the matching rosbags. One bag per fault gives you a snapshot. With all of them you can replay the mission, see whether the problem is systematic, and see what to fix on the robot. We should have flagged the change before merging it.

PostgreSQL was never on our roadmap. We picked SQLite because medkit runs on the robot itself and we wanted storage with no daemon and no external dependency. Once you offload to remote infrastructure, SQLite stops being the right tool. We don't see a fundamental drawback. The only cost is a second backend to keep green in CI, and we're fine with that. So yes, we want PostgreSQL in medkit and we'll support it after the PR lands.

On stability: no schema changes are planned. A bug could still force one and I can't promise otherwise, but nothing is queued. Three things we can offer:

1. Open a draft PR whenever you want, even half finished. Once it's open we can see what you're touching and review as you go.
2. We won't merge anything that touches the DB schema or the filesystem layout while your PR is open without raising it here first.
3. Join our Discord: https://discord.com/invite/6CXPMApAyq. We haven't been announcing breaking changes but from now on we will announce them there before they land.

After your PR is merged I want to put a storage interface in front of the database, so the backend and the schema can be swapped without touching the rest of medkit. The storage layer assumes SQLite everywhere, which is why your integration keeps breaking on our changes. If you can keep backend specific code isolated where that's practical, we can pull the interface out of the two implementations instead of designing it up front.

[gstavrinos]

Hey Bartosz,

Thank you for the lightning fast and insightful reply.

I will open a PR as soon as I have the basic integration ready. I will work on the tests while the PR is open, so nothing will be tested at that point.

Thanks again. Talk to you soon (hopefully) after the PR!

George