I use postgres pgbench to test cockroachdb,bug error

General
#1

I run ./pgbench -i pgbench --username=test --host=192.168.8.73 --port=26260
but error:
ERROR: syntax error at or near "with"
DETAIL: source SQL:
create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=100)

so why not support with ?

How do i know tps of cockroachdb basetest?

1 Like
#2

Dear mingbao,

thank you for your interest in CockroachDB!

CockroachDB does not yet support the WITH syntax (common table expressions); this is tracked in https://github.com/cockroachdb/cockroach/issues/7029

You can measure “transactions by second” by writing your own client and issuing SQL transactions to CockroachDB directly.

Hope this helps!

#3

@knz that isn’t actually a CTE, just pg-specific storage options passed at table creation.

@mingbao 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 pgbench without -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.

#4

Where is this code located now? The links above do not work.