Quality Assurance in PostgreSQL^® with SQLsmith

An interesting tool for quality assurance of C compilers is Csmith, a generator of random C programs.

I had come to appreciate it during the development of an optimization phase for a C compiler. This compiler features extensive regression tests, and the correctness of the translated programs is also tested using standardized benchmarks. The latter, for example, had uncovered a bug in my optimization because a chess program included in the benchmark collection, which was compiled with the compiler, made a different move than expected when playing against itself.

Eventually, however, these resources were exhausted, and everything seemed ready for delivery. I then remembered Csmith, which I had rather scoffed at until that point. “It can’t hurt to run it once.” To my surprise, it found further critical bugs in my code that had not been revealed by the other tests.

When my professional path led back to databases, I missed such a tool for PostgreSQL^® development. One year and 269 commits later, I can now announce the first release of SQLsmith 1.0.

SQLsmith generates random SQL queries, taking into account all tables, data types, and functions present in the database. Due to their randomness, the queries are often structured significantly differently than one would write them “by hand” and therefore uncover many edge cases in the Optimizer and Executor in PostgreSQL^® that would otherwise never be tested.

Already during development, SQLsmith has found 30 bugs in PostgreSQL^®, which were promptly corrected by the PostgreSQL^® community.

Anyone who programs extensions for PostgreSQL^®, or generally develops for PostgreSQL^®, now has an additional debugging tool available with SQLsmith. Users also benefit from SQLsmith through the additional quality assurance that now takes place during PostgreSQL^® development.

The source code for SQLsmith is available under GPLv3 on GitHub.

This article was originally authored by Andreas Seltenreich.