Andreas Seltenreich's sqlsmith tool has found an impressive number of bugs. In light of that, it seems to me that it would be reasonable for a contributor to write a regression test that avoids dropping database objects specifically so that sqlsmith had some chance of finding bugs after the fact, by generating a query whose plan ends up accessing said objects. Commit 975ad4e6 fixed a nasty bug in BRIN that was originally found by Andreas' fuzz testing. Perhaps that bug would still be around if Alvaro had included any DROP statements within brin.sql. Presumably his choice to not to do so was completely arbitrary. Actually, it seems likely that it was a fortunate accident.
I understand that there is a cost to increasing the high-watermark amount of disk space used during regression testing, since many buildfarm animals are rather underpowered. I also understand that in most cases the objects themselves aren't really what we're testing, so it doesn't make much sense to leave them behind. However, it still seems like a good idea to try to think about this a little more strategically. For example, the new index_including.sql file drops all INCLUDE indexes/tables proactively, even though it looks like they're rather small, and in a certain sense worth keeping around. I also see that both spgist.sql and gin.sql avoid dropping objects, but gist.sql seems to drop several. hash_index.sql drops some objects, too (sqlsmith also found hash index bugs despite this). Maybe a cheap, scalable solution is possible. For example, perhaps we could maintain a version of the regression tests that have Postgres somehow silently ignore most DROP statements. I doubt that people like Andreas will ever be remotely concerned about the extra disk space overhead, since fuzzing is inherently a computationally intensive process; CPU costs are bound to dominate. A strategy like this would also make it easier to run amcheck on indexes left behind by the regression tests. How can we do better? -- Peter Geoghegan
