current_setting

Applies to VAST Cluster 5.5.

Functions › Session and utility functions › Session and diagnostics. Returns the current value of a Query Engine setting, given its name as a constant string. The result has the setting's own type: a number for a numeric setting, a string for a text setting.

Syntax

current_setting('setting_name')

Returns: the setting's type, such as BIGINT or VARCHAR.

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_current_setting. 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 small table that holds setting names, to show what happens when the name comes from a column:

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

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}.settings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES (1, 'default_order'), (2, 'search_path'), (3, NULL)")

[]
[]
[(3,)]

What it does

The name is checked when the query is parsed, so it must be a constant string. The result has the setting's own type:

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 current_setting('THREADS') = current_setting('threads')")
run("SELECT current_setting(NULL)")
run("SELECT current_setting('')")
run("SELECT current_setting('no_such_setting_xyz')")
run("SELECT current_setting('TimeZone')")
run("SELECT current_setting(123)")

[(True,)]
InternalError: Parse query: 'SELECT current_setting(NULL)' failed with: Parser Error: Key name for current_setting needs to be a constant string
InternalError: Parse query: 'SELECT current_setting('')' failed with: Parser Error: Key name for current_setting needs to be neither NULL nor empty
InternalError: Catalog Error: unrecognized configuration parameter "no_such_setting_xyz"
InternalError: Catalog Error: Setting with name "timezone" is not in the catalog, but it exists in the icu extension.
InternalError: Binder Error: No function matches the given name and argument types 'current_setting(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Read three text settings:

run("SELECT current_setting('default_order'), current_setting('default_collation'), "
    "current_setting('search_path')")

[('asc', '', '')]

The type of each result:

run("SELECT typeof(current_setting('threads')), typeof(current_setting('default_order'))")

[('BIGINT', 'VARCHAR')]

A name taken from a column fails, because it isn't a constant:

run(f"SELECT id, current_setting(name) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Parse query: 'SELECT id, current_setting(name) FROM "<bucket>/docs_sql_fn_string_current_setting".settings ORDER BY id LIMIT 10' failed with: Parser Error: Key name for current_setting needs to be a constant string

How it runs in VAST

The Query Engine runs on the cluster's CNodes. current_setting reads a setting of the Query Engine, not data, so it needs no table. The name is read when the query is parsed, which is why a column, a NULL or an empty string fails before the query runs. Used in a query on a table, the value is the same for every row:

run(f"SELECT id, current_setting('default_order') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'asc'), (2, 'asc'), (3, 'asc')]

Make it faster

• Read a setting in a query of its own. The value is the same for every row, so there's no need to return it with each one: SELECT current_setting('default_order').

Limitations and errors

• The name must be a constant. A column fails with Key name for current_setting needs to be a constant string. To read several settings, list them in one SELECT, one call each.
• Unknown names fail. An unknown name fails with unrecognized configuration parameter; the full message suggests a similar name.
• Time zone. current_setting('TimeZone') fails: the setting isn't available in the Query Engine.

Similar functions

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

[('', None, True)]

Clean up:

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

[]
[]

See also

• Functions: all the function families.
• SQL reference: connecting, statements and data types.