@knz that isn’t actually a CTE, just pg-specific storage options passed at table creation.
pgbench does an init step when you pass
-i where it creates tables before it runs the load generation step to measure performance. Unfortunately, the table creation step uses postgres-specific storage options. If you create the schema yourself, you can run off-the-shelf
-i and it should work. Indeed, we’ve done this ourselves, and have the edited schema here: https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/pgbench/setup.go#L25
Once you have the tables though, you need some rows in them for it to run against, which usually are also created by
-i so we made a little program to create the tables and fill them the same way
pgbench -i would but without the pg options. If you have a go toolchain, you can
go install github.com/cockroachdb/cockroach/pkg/sql/pgbench/cmd/pgbenchsetup and then run
pgbenchsetup postgres://<your db> to set it up for you. If not though, you can just paste that schema above and then insert the rows by hand into the 4 tables.
Either way, once you have the tables setup, you’ll want to run
pgbench without the
-i option and with the
-n option, which disables sending vacuum commands, which are another postgres-speciifc storage optimization and aren’t recognized by cockroach.