What is our SQL testing strategy?

[ewjoachim]

trafficstars

Taking things from #183:

• We do need tests for the SQL. This is something we don't really have.
• We planned to have migration/schema tests with pum, but we never set those up. Ping @elemoine
• I'm afraid of generating automatically our tests from the code: if there's an error in the code, there will be one in the tests too. I consider that testing that our migrations and our schema produce the same database is rather something pum should be doing.
• That being said, it can be a way to make it harder to accidentally remove something we shouldn't. In that sense, it could be useful, but I'd love more arguments on good reasons to do that (describe our DB twice and manually sync those 2 descriptions)
• What would really be useful would be testing the behavior of the functions, procedures and triggers. Currently, this is something we do partially, and very coupled with Python.
• From the test framework list page in the postgres wiki, I've found https://postgrespro.github.io/testgres/ . I have no idea if it does what we want, but it could be interesting to try out.
• If it works the way we want, it could be interesting to have a SQL section in the tests and to test both definitions (if deemed useful) and behaviour. I'd rather not have pytest, PGTap and a 3rd test framework. Ideally 1, and if we really need a different one for pg, then 2, not 3. (if we can behaviour-test our procedures with PGTap without it being hell for contributors, it can be nice too, but let's not forget any new tool we add will be something contributors will need to adopt too)

From @marco44 :

For test of plpgsql, maybe you could use a combination of pgtap for calling the functions and verifying the results, as it's exactly what it is intended for, and plpgsql_check, which can give you coverage measurements on your PL. I never tested this last part though.