nextval

Applies to VAST Cluster 5.5.

Functions › Session and utility functions › Session and diagnostics. nextval(name) returns the next value of a sequence and currval(name) the value nextval last returned. The VAST Query Engine doesn't support CREATE SEQUENCE, so there's no sequence for them to read: both fail unless the name is NULL. To number rows, use row_number; to generate unique keys, use gen_random_uuid.

Syntax

nextval(sequence_name)
currval(sequence_name)

Returns: BIGINT.

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_string_nextval. 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 orders. The last row has no item:

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_string_nextval"'

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 your database name shown as <bucket>."""
    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).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.orders"
TICKETS = f"{SCHEMA}.tickets"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, item VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES (1, 'disk'), (2, 'cable'), (3, NULL)")

[]
[]
[(3,)]

What it does

nextval and currval look up a sequence object by name. A sequence is created with CREATE SEQUENCE, which the VAST Query Engine doesn't support: it fails with The operator 'CREATE_SEQUENCE' type=52 is currently not supported. No sequence can exist, so nextval and currval fail with Sequence with name ... does not exist! for any name:

run(f"CREATE SEQUENCE {SCHEMA}.docs_seq START 1")
run("SELECT nextval('docs_seq')")
run("SELECT currval('docs_seq')")

InternalError: The operator 'CREATE_SEQUENCE' type=52 is currently not supported.
InternalError: Catalog Error: Sequence with name docs_seq does not exist!
InternalError: Catalog Error: Sequence with name docs_seq does not exist!

NULLs and edge cases

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

run("SELECT nextval(NULL)")
run(f"SELECT id, nextval('docs_seq') FROM {TABLE} ORDER BY id LIMIT 10")
run(f"CREATE TABLE {SCHEMA}.seq_t (id BIGINT DEFAULT nextval('docs_seq'), v VARCHAR)")

[(None,)]
InternalError: Catalog Error: Sequence with name docs_seq does not exist!
InternalError: Catalog Error: Sequence with name docs_seq does not exist!

Examples

Number the rows of a result with row_number() OVER (). Sort them in a subquery first, with ORDER BY ... LIMIT, because ORDER BY inside OVER isn't available. Add an offset to start the numbers where you want:

run(f"SELECT id, item, 1000 + row_number() OVER () AS order_no "
    f"FROM (SELECT id, item FROM {TABLE} ORDER BY id LIMIT 100)")

[(1, 'disk', 1001), (2, 'cable', 1002), (3, None, 1003)]

Give each row a unique key when you write it: store gen_random_uuid() in a UUID column. Each call returns a new value, so no two rows share a key:

run(f"CREATE TABLE IF NOT EXISTS {TICKETS} (ticket UUID, item VARCHAR)")
run(f"INSERT INTO {TICKETS} SELECT gen_random_uuid(), item FROM {TABLE}")
run(f"SELECT count(*), count(ticket) FROM {TICKETS}")
run("SELECT typeof(gen_random_uuid()), gen_random_uuid() = gen_random_uuid()")

[]
[(3,)]
[(3, 3)]
[('UUID', False)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. It looks a sequence name up in its catalog when it plans the query, before any scan starts in VAST DataBase. The lookup fails, so the query returns the Catalog Error and reads no rows, whether nextval is called on its own, in the SELECT list of a table query, or in a column default. A NULL name has nothing to look up, and the result is NULL.

row_number() OVER () and gen_random_uuid() are computed by the Query Engine for each row the scan returns. The plan of the numbering example has no FILTER step; the numbering runs in the Query Engine above the sorted subquery:

plan(f"SELECT id, row_number() OVER () FROM (SELECT id FROM {TABLE} ORDER BY id LIMIT 100)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      STREAMING_WINDOW     │
│    ────────────────────   │
│        Projections:       │
│    ROW_NUMBER() OVER ()   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           TOP_N           │
│    ────────────────────   │
│          Top: 100         │
│                           │
│         Order By:         │
│      vastdb."<bucket>     │
│/docs_sql_fn_string_nextval│
│      ".orders.id ASC      │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Generate keys once, when you write. Store the gen_random_uuid() value, or a key your application assigns, in a column. A comparison of that column with a constant is pushed down to VAST DataBase; a key computed again at query time isn't.
• Number only the rows you need. Filter and LIMIT in the subquery, so row_number() runs on fewer rows.

Limitations and errors

• No sequences. CREATE SEQUENCE fails with The operator 'CREATE_SEQUENCE' type=52 is currently not supported., and nextval and currval fail with Catalog Error: Sequence with name ... does not exist!. Use row_number() or gen_random_uuid(), or assign IDs in your application.
• No auto-increment column. A column default of nextval(...) fails in the same way. Write the key with each row instead.
• row_number() numbers a result, not a table. The numbers start at 1 in each query; OVER (ORDER BY ...) and PARTITION BY fail with The operator 'WINDOW' type=6 is currently not supported. See row_number.

run(f"SELECT id, row_number() OVER (ORDER BY id) FROM {TABLE}")

InternalError: The operator 'WINDOW' type=6 is currently not supported.

Similar functions

run("SELECT nextval('docs_seq')")
run("SELECT currval('docs_seq')")
run(f"SELECT id, row_number() OVER () FROM (SELECT id FROM {TABLE} ORDER BY id LIMIT 100)")
run("SELECT typeof(gen_random_uuid())")

InternalError: Catalog Error: Sequence with name docs_seq does not exist!
InternalError: Catalog Error: Sequence with name docs_seq does not exist!
[(1, 1), (2, 2), (3, 3)]
[('UUID',)]

Clean up:

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

[]
[]
[]
[]

See also

• row_number and gen_random_uuid.
• Functions: all the function families.
• EXPLAIN: how to read a plan.