gen_random_uuid

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › UUIDs. Returns a new random version 4 UUID each time it's called, and a different one for each row. uuid is the same function. Use it to give rows an identifier when you write them.

Syntax

gen_random_uuid()
uuid()

The functions take no arguments.

Returns: UUID. The client receives it as a Python uuid.UUID object.

Aliases: uuid.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_binary_hash_uuid_gen_random_uuid. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of three items. Rows 1 and 2 get a UUID from gen_random_uuid() and uuid() as they are written; row 3 has none:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_binary_hash_uuid_gen_random_uuid"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.items"
V4 = "'^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, u UUID, label VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES (1, gen_random_uuid(), 'first'), (2, uuid(), 'second'), (3, NULL, 'third')")

[]
[]
[(3,)]

V4 is the pattern of a version 4 UUID in text form: the third group starts with 4, and the fourth with 8, 9, a or b. The examples test values against it instead of printing them, because every run gives new values.

What it does

gen_random_uuid() returns a random UUID of version 4, the version made of random bits. uuid() does the same.

• A new value for each call and each row. Two calls in the same expression give different values, and a query over a table gives each row its own value.
• The result is a UUID, not text. Store it in a UUID column, or cast it to VARCHAR for the 36-character form with hyphens, in lowercase.
• It's computed when the query runs. A value you SELECT isn't kept: write it to a table to use it again.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT gen_random_uuid() IS NULL, gen_random_uuid() = gen_random_uuid(), "
    "typeof(gen_random_uuid()), typeof(uuid())")
run(f"SELECT length(CAST(gen_random_uuid() AS VARCHAR)), substring(CAST(uuid() AS VARCHAR), 15, 1), "
    f"regexp_matches(CAST(gen_random_uuid() AS VARCHAR), {V4})")
run("SELECT gen_random_uuid(1)")
run("SELECT uuid('x')")

[(False, False, 'UUID', 'UUID')]
[(36, '4', True)]
InternalError: Binder Error: No function matches the given name and argument types 'gen_random_uuid(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'uuid(STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Check the UUIDs written by the setup block. Rows 1 and 2 hold version 4 UUIDs; row 3 has none:

run(f"SELECT id, u IS NOT NULL, regexp_matches(CAST(u AS VARCHAR), {V4}) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, True), (2, True, True), (3, False, None)]

Write 1,000 rows with a UUID each in one statement, using the range table function, and check them. Every row gets a value of the version 4 form:

run(f"INSERT INTO {TABLE} SELECT 100 + x, gen_random_uuid(), 'bulk' FROM range(1000) t(x)")
run(f"SELECT count(*), count(u), sum(CASE WHEN regexp_matches(CAST(u AS VARCHAR), {V4}) THEN 1 ELSE 0 END) "
    f"FROM {TABLE} WHERE id >= 100")

[(1000,)]
[(1000, 1000, Decimal('1000'))]

Give the row that has no UUID one, with UPDATE:

run(f"UPDATE {TABLE} SET u = gen_random_uuid() WHERE id = 3")
run(f"SELECT id, u IS NOT NULL FROM {TABLE} WHERE id = 3")

[(1,)]
[(3, True)]

Two calls in one row give different values. Out of 100 rows with two UUIDs each, none has a matching pair:

run("SELECT count(*) FROM (SELECT gen_random_uuid() AS a, gen_random_uuid() AS b FROM range(100)) WHERE a = b")

[(0,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. gen_random_uuid() reads no data: the Query Engine computes a new value for every row it produces.

• In the SELECT list or in INSERT ... SELECT, it's computed for each row the query returns or writes.
• In WHERE, a condition that uses it, such as u = gen_random_uuid(), is a FILTER step in the Query Engine. Unlike a constant, it isn't computed once before the scan, so the comparison isn't pushed down to VAST DataBase.

The plan of such a condition shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE u = gen_random_uuid()")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (u = gen_random_uuid())  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             u             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Generate the UUID when you write the row, with INSERT or UPDATE as in the examples, and keep it in a UUID column. Reading it back costs nothing extra, and a comparison of the column with a UUID constant is pushed down to VAST DataBase (UUID values and casts).
• Write many rows in one statement. INSERT ... SELECT ..., gen_random_uuid() FROM range(n) creates all the rows and their UUIDs in one query.

Limitations and errors

• Version 4 only. uuidv4, uuidv7, uuid_generate_v4, uuid_extract_version and uuid_extract_timestamp fail with Scalar Function with name ... does not exist!. Use gen_random_uuid() for version 4 UUIDs, and generate other versions in your application.
• No arguments. gen_random_uuid(1) and uuid('x') fail with No function matches the given name and argument types.
• No duplicate check with GROUP BY. GROUP BY isn't available in the VAST Query Engine (The operator 'HASH_GROUP_BY' type=9 is currently not supported.), so you can't count distinct UUIDs in SQL. If your application needs a uniqueness guarantee, check it when you load the data.

run("SELECT uuidv7()")
run(f"SELECT u, count(*) FROM {TABLE} GROUP BY u ORDER BY u LIMIT 10")

InternalError: Catalog Error: Scalar Function with name uuidv7 does not exist!
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

Similar functions

run("SELECT typeof(gen_random_uuid()), typeof(uuid()), typeof('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID), "
    "typeof(CAST(gen_random_uuid() AS VARCHAR))")

[('UUID', 'UUID', 'UUID', 'VARCHAR')]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Binary, hash and UUID functions: all the UUID functions.
• UUID values and casts: UUID constants, text forms and comparisons.
• Data types in SQL: UUID columns.
• EXPLAIN: how to read a plan.