in_search_path

Applies to VAST Cluster 5.5.

Functions › Session and utility functions › Session and diagnostics. Returns true if the schema schema_name of the database database_name is in the Query Engine's search path, and false if it isn't.

Syntax

in_search_path(database_name, schema_name)

Returns: BOOLEAN.

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_in_search_path. 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 database and schema names to test. The last row has no values:

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

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}.paths"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, db VARCHAR, sch VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'system', 'main'), "
    "(2, 'temp', 'main'), "
    "(3, 'memory', 'main'), "
    "(4, 'no_db', 'no_schema'), "
    "(5, NULL, 'main')")

[]
[]
[(5,)]

What it does

in_search_path checks one pair of names against the search path and returns a BOOLEAN. Names that don't exist aren't an error: they return false. Both arguments can be columns, so you can test many pairs in one query.

The setting search_path, which current_setting reads, is an empty string on a new connection, as the last example shows. The examples in these docs name tables in full, as "bucket/schema".table.

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 in_search_path(NULL, 'main'), in_search_path('memory', NULL), in_search_path('system', 'main'), "
    "in_search_path('temp', 'main'), in_search_path('memory', 'main'), in_search_path('no_db', 'no_schema'), "
    "in_search_path('', '')")
run("SELECT in_search_path(123, 'main')")

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

Examples

Test each pair in the table:

run(f"SELECT id, db, sch, in_search_path(db, sch) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'system', 'main', True), (2, 'temp', 'main', True), (3, 'memory', 'main', False), (4, 'no_db', 'no_schema', False), (5, None, 'main', None)]

Test your own bucket and the examples' schema:

run(f"SELECT in_search_path('{BUCKET}', 'docs_sql_fn_string_in_search_path'), current_setting('search_path')")

[(False, '')]

Keep the pairs that are in the search path:

run(f"SELECT id, db, sch FROM {TABLE} WHERE in_search_path(db, sch) ORDER BY id LIMIT 10")

[(1, 'system', 'main'), (2, 'temp', 'main')]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). in_search_path() is a function of columns, so it's computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes the result for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine keeps the rows that pass.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE in_search_path(db, sch)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  in_search_path(db, sch)  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             db            │
│            sch            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Combine it with a pushed-down condition. In id > 100 AND in_search_path(db, sch), id > 100 can be evaluated in VAST DataBase, so the function runs only on the rows that pass it. See pushdowns.

Limitations and errors

• Two VARCHAR arguments. A number fails with No function matches the given name and argument types.
• Unknown names return false, not an error. Check the spelling of a name before you rely on a false.

Similar functions

run("SELECT in_search_path('system', 'main'), current_setting('search_path')")

[(True, '')]

Clean up:

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

[]
[]

See also

• current_setting: read a Query Engine setting.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.