Inline SQL Commands

When connecting to the database, Heimdall exposes a few commands that are handled directly by Heimdall vs. by the database. Note: These interceptions ONLY occur when using the simple query protocol via a tool such as PSQL. If your tool uses a prepared interface, as many GUI tools will, these command will not be intercepted.

Show commands

The key use cases for Heimdall database proxy include:

(Postgres only)

  • show databases
  • show schemas
  • show tables
  • show users

All databases:

  • show pools: Displays a list of user pools and relevant metrics.
  • show connections: Displays a list of connections to Heimdall VDBs, along with the following columns and their descriptions:
    • Con ID: Unique identifier for each connection.
    • Conn Age(s): Duration the connection with Heimdall has been active, measured in seconds.
    • Idle(s): This represents the duration in seconds when the connection remained inactive.
    • Start(s): Elapsed time in seconds since the start of the current query execution, or "NA" if no query is currently running.
    • User: Username associated with the connection.
    • Catalog: Name of the current catalog.
    • Autocommit: Current state of the auto-commit mode for this connection object.
    • sourceName: Name of the datasource to which the connection is established ("none") if there is no underlying datasource connection.
    • Server URL: URL used to connect to the datasource ("none") if there is no underlying datasource connection.
    • Con properties: Properties of the VDB connection ("none") if there is no underlying datasource connection.
    • Tables: Tables involved in the currently executed query.
  • show querytracker <tablename> <count>: Displays relevant tracking information for queries tracked in Heimdall, table and count are optional. Only used for cache auto-repopulation (rarely used)
  • show queryinfo: Displays attributes associated with the previous query.
  • show maxrows: Shows a connection limit on the number of rows any given result-set will try to pull. The default value is 0, meaning the full result-set will be pulled.
  • show cache <key>: Displays cache attributes associated with the given key. Doesn't matter whether key starts with "hdkey" prefix.

Control Commands

  • set querytimeout: Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds. If the limit is exceeded, an SQLException is thrown.
  • set cachepool: Adds a value to the cache key to provide unique cache "pools" depending on external factors, unobserved by Heimdall
  • set maxrows: Sets a connection limit on the number of rows any given result-set will try to pull, as implemented via the jdbc "setMaxRows()" api.

Other Commands

  • drop connection: When in proxy mode, force the connection to drop--useful for using test tools that don't close connections on their own.
  • clear pool: Clear server-side connections for current user.
  • clear pool <userpool>: Clear server-side connections for a particular user pool, <poolname> must be the username:databasename as displayed in the show pools command.
  • reset query cache: Reset all query cache for a virtual database.
  • reset query cache <tablename1>,<tablename2>: Reset query cache for all listed table names in CSV format.

Note: To issue clear pool, the user must clear their own pool or be authenticated as one of the users: "postgres", "gpadmin", "root", "sa" or "admin". This will be changed to be configurable in the future.