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.
The key use cases for Heimdall database proxy include:
- show databases
- show schemas
- show tables
- show users
- show pools: Displays a list of user pools and relevant metrics.
- show connections
- 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.
- 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.
- 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.