94027: sql: new functions `crdb_internal.generate_test_objects` and `.gen_rand_ident` r=postamar a=knz Fixes #94026. tldr: the new function ``` crdb_internal.generate_test_objects(<parameters>) ``` generates multiple SQL objects at once. It is meant for use by tests. Additionally, a new flag `--expand-schema` was added to `cockroach demo` which exercises and demonstrates this new feature. When specified, `demo` will gradually *multiply* the workload schema by the specified number of schema objects, by adding databases and tables similar to the one defined by the workload. For example: ``` $ cockroach demo movr --expand-schema=10000 ... wait a bit ... > SHOW DATABASES; ... observe: similar databases to movr have been created, 100 tables each ``` ---- The parameters argument is a JSONB object, containing at least the following fields: - `"counts"`: the counts of objects to create. - `"names"`: the pattern to use to name generated objects. The pattern and counts array influences object creation as follows: | Counts | Pattern | Creates databases? | Creates schemas? | Creates tables? | Total objects created | |------------|-------------|--------------------|---------------------------|-------------------------------------------|---------------------------------------| | N | `tb` | no | no | yes: `<currentdb>.<currentschema>.tb1`... | N | | N | `sc.tb` | no | no | yes: `<currentdb>.sc.tb1`... | N | | N | `db.sc.tb` | no | no | yes: `db.sc.tb1`... | N | | N2, N1 | `sc.tb` | no | yes: `<currentdb>.sc1`... | yes: `<currentdb>.sc1.tb1`... | N2 + (N1 x N2) | | N2, 0 | `sc.tb` | no | yes: `<currentdb>.sc1`... | no | N2 | | N2, N1 | `db.sc.tb` | no | yes: `db.sc1`... | yes: `db.sc1.tb1`... | N2 + (N1 x N2) | | N3, N2, N1 | `db.sc.tb` | yes: `db1`... | yes: `db1.sc1`... | yes: `db1.sc1.tb1`... | (N3 x 2) + (N3 x N2) + (N1 x N2 x N3) | | N3, N2, 0 | `db.sc._` | yes: `db1`... | yes: `db1.sc1`... | no | (N3 x 2) + (N3 x N2) | | N2, 0 | `db.sc._` | no | yes: `db.sc1`... | no | N2 | | N3, 0, 0 | `db._._` | yes: `db1`... | no | no | N3 x 2 | | N1, 0, N2 | `db.tb` | yes: `db1`... | no | yes: `db1.public.tb1`... | (N3 x 2) + (N3 x N2) | | 0 | `tb` | no | no | no | 0 | | 0, N2 | `prefix.tb` | no | no | no | 0 | | 0, N2, N1 | `db.sc.tb` | no | no | no | 0 | | 0 | `prefix.tb` | no | no | no | 0 | | 0 | `db.sc.tb` | no | no | no | 0 | | 0, N1 | `db.sc._` | no | no | no | 0 | NB: the reason why the requested number of databases is doubled in the total count of objects created is that a `public` schema is created for each new database. The convenience overload `generate_test_objects(pattern : string, count : integer)` is also provided as a convenience alias for `generate_test_objects('{"names":pattern, "counts":[count]}'::JSONB)`. Likewise, `generate_test_objects(pattern : string, counts : []integer)` is also provided as a convenience alias for `generate_test_objects('{"names":pattern, "counts":counts}'::JSONB)`. The configuration parameters may also contain the following fields: - `"names"`: pattern to use to name the generated objects (default: "_", see note about `"table_templates"` below). - `"counts"`: counts of generated objects (default: [10]). - `"dry_run"`: prepare the schema but do not actually write it (default: false). - `"seed"`: random seed to use (default: auto). - `"randomize_columns"`: whether to randomize the column names on tables (default: true). - `"table_templates"`: table templates to use. If the last part of `"names"` is "_", the name of the template will be used as base pattern during name generation for tables. Otherwise, the last part of "names" will be used as pattern. If no table templates are specified, a simple template is used. - `"name_gen"`: configuration for the name generation, see below. Guaranteed properties: - when a requested object count is zero, no object of that type (and no object of sub-types) gets created. This preserves the mathematical properties: - calls with a count of zero are idempotent. - if the function is called sequentially with counts N and M, the total number of objects is always N+M regardless of the values of N and M. Also for testing and troubleshooting, another function is provided: ``` crdb_internal.gen_rand_ident(name_pattern: string, count: int, parameters: jsonb) -> string ``` It produces SQL identifiers using the same algorithm as used by `generate_test_objects`. Its parameters are: - `"seed"`: the seed to use for the pseudo-random generator (default: random). -` "number"`: whether to add a number to the generated names (default true). When enabled, occurrences of the character '#' in the name pattern are replaced by the number. If '#' is not present, the number is added at the end. - `"noise"`: whether to add noise to the generated names (default true). It adds a non-zero probability for each of the probability options below left to zero. (To enable noise generally but disable one type of noise, set its probability to -1.) - `"punctuate"`: probability of adding punctuation to the generated names. - `"quote"`: probabiltiy of adding single or double quotes to the generated names. - `"emote"`: probability of adding emojis to the generated names. - `"space"`: probability of adding simple spaces to the generated names. - `"whitespace"`: probability of adding complex whitespace to the generated names. - `"capitals"`: probability of using capital letters in the generated names. Note: the name pattern must contain ASCII letters already for capital letters to be used. - `"diacritics"`: probability of adding diacritics in the generated names. - `"diacritic_depth"`: max number of diacritics to add at a time (default 1). - `"zalgo"`: special option that overrides diacritics and diacritic_depth (default false). For convenience, the overload `crdb_internal.gen_rand_ident(name_pattern: string, count: int)` is provided as an alias to `gen_rand_ident(pattern, count, '{}'::JSONB)`. Release note: None 94781: roachtest: use fresh virtualenv for python tests r=ZhouXing19 a=rafiss fixes #91351 backport fixes #91347 and #91343 The sqlalchemy tests were failing because the wrong depenencies were in the virtualenv leftover from a previous test run. Release note: None Co-authored-by: Raphael 'kena' Poss <knz@thaumogen.net> Co-authored-by: Rafi Shamim <rafi@cockroachlabs.com>

sql: generate many tables quickly #94026

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions