Creating a script for CockroackDB

I am trying to create a script to create tables and inserting rows. I want to create a simulated traffic to the cluster without using the TPC-C, since I need to know exactly what the data I am inserting looks like. This is what I am trying:

CREATE TABLE student (
    id int,
    student VARCHAR,
    age int

);

declare @id int 
select @id = 1
while @id >=1 and @id <= 1000
begin
    insert into student values(@id, 'thesis' + convert(varchar(5), @id), 12)
    select @id = @id + 1
end

Then I pipe it into my cluster by doing:

cockroach sql --insecure < my-file.sql

However, I get this error:

ERROR: at or near "declare": syntax error: unimplemented: this syntax
SQLSTATE: 0A000
DETAIL: source SQL:
declare @id int
^
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/41412/v20.2

If anyone got an example script that I could use as guidance in how to build a working script, It would be much appreciated.

I’ve gotten this far now.

CREATE SEQUENCE blog_posts_id_seq;
CREATE TABLE blog_posts (
  id INT PRIMARY KEY DEFAULT nextval('blog_posts_id_seq'),
  -- ^ in Postgres, `id SERIAL` expands to the above
  --   except with int4 instead of our int, which is 8 bytes
  title text,
  body text
);
INSERT INTO blog_posts (title, body) VALUES ('Sequences', 'Whooo') RETURNING (id);
-- => id: 0
INSERT INTO blog_posts (title, body) VALUES ('They''re awesome', 'Yep') RETURNING (id);
-- => id: 1
-- etc

Unfortunately I can’t get WHILE, LOOP or DECLARE to work. So I am just going to insert 1000 rows with insert. However, now I need to make the inserts go slower, so I’ve tried to sleep(5) or WAITFOR DELAY '00:01' but it is not supported.

UPDATE:

pg_sleep(2) worked.

Cockroach does not support procedures. I don’t know if you’re ultimately going to be success without writing code to issue the sql using a driver. You could unroll your loop and put pg_sleep between the inserts I suppose.

One thing to note is that you can use sql sequences like Postgres for the SERIAL column but it will come with real costs.
See the docs here: SERIAL | CockroachDB Docs

On a more meta point, what really are you trying to do when you say

I want to create a simulated traffic to the cluster without using the TPC-C, since I need to know exactly what the data I am inserting looks like.

The tpcc workload included in cockroach is open source and flexible. It’s not hiding anything and it is reasonable to understand exactly what data you are inserting. What is your high level goal with whatever you’re doing?

I am trying to create a high level chaos testing. I want to write and read data into my cluster while simultaneously killing off 1 or 2 nodes. For that I am planning to write a script that performs all of this. Either from a sql-script or from a bash-script.

I am probably making it more complicated than it needs to be, since it is just a high level testing I am aiming for.

I have been looking at these

cockroach gen example-data --help

But since I can’t control how fast the data in bank , kv, intro are being inserted, and I can’t kill off nodes from the same command. So I am trying to make a home-brewed script to do it all in one sweep.

Ok, solved it. Gonna read up on the SERIAL. Probably gonna make the same test with and without using SERIAL since it’s costly.