How does Cockroach test for Postgres compatibility?

[anyweez]

Given the goal of Postgres compatibility, I'm curious if Cockroach maintains a test suite from a connecting client's perspective (list of sql queries, Postgres wire protocol messages, etc) to ensure compatibility is maintained over time. Is there anything in this repository or elsewhere that I could study?

I'm looking for something like this for a separate project and would love to lean on (and contribute to!) your existing work if possible.

[jordanlewis]

CockroachDB has a lot of such test suites. Here are some:

Logic Tests are probably the largest and most actively developed SQL test suite in CockroachDB. The Logic Test framework is a datadriven (plain text file based) expectation testing system. The test files live here:(https://github.com/cockroachdb/cockroach/tree/master/pkg/sql/logictest/testdata/logic_test
The logic test framework was adapted from SQLite’s logic test framework which is covered below.

Logic tests support “configurations”, which are keywords that instruct the test framework to behave differently. By default, each logic test file is run under a large set of configurations, which test different modes of operation in the database.

The PGWire protocol is tested with a datadriven test suite that contains expectations for how the server should process sequence of low-level Postgres wire protocol messages.

Not specifically Postgres, but SQLite has a large suite of tests that we use in CockroachDB. They take several hours to run. They’re logic tests: they take the form of SQL inputs and expected outputs. Cockroach maintains a fork of these tests here: cockroachdb/sqllogictest

We also run driver and ORM test suites from external products, asserting that they pass (except for tests that we've explicitly added to a blocklist). An example of such a test that also happens to be quite complete and effective is the PGJDBC test suite, which you can see how we orchestrate here: https://github.com/cockroachdb/cockroach/blob/master/pkg/cmd/roachtest/tests/pgjdbc.go

CockroachDB also uses many randomized testing techniques. One of them is SQLSmith. We've written an extension to SQLSmith, called the "Compare test", that runs SQLSmith queries and compares the results between CockroachDB and Postgres. You can find it here: Compare Test

We've been intending to write a more comprehensive blog post about how we test CockroachDB, but these are some good starting points for your question. Hope it helps and please share back with other ideas that you might come up with for your product - we're always looking for more ways to increase our compatibility testing with Postgres.

[anyweez]

Thanks @jordanlewis - fantastic answer! Thank you for the details, this is exactly what we were looking for. Will certainly provide feedback as we come across it.