Stashing Previously Set psql Variables

The command-line based "PostgreSQL interactive terminal" known as psql is handy for manipulating and accessing data in a PostgreSQL database. Because of its command-line nature, psql is particularly well suited for use in scripts. One of the psql features that makes it even more useful in scripting contexts is its support for "meta-commands". As the psql documentation states, "Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that" and "these commands make psql more useful for administration or scripting."

When writing psql scripts, it is often preferable to set some variables locally for the time period the script is being run, but might also be desirable to not change these variables permanently for the psql session if it's likely that other scripts or other work will be performed from the psql session after the script's conclusion. In this post, I will demonstrate use of psql's \set meta-command to temporarily store off previous settings of variables to restore these settings at the script's conclusion.

The psql documentation describes "a number of ... variables [that] are treated specially by psql." These "specially treated variables" are the ones that we most likely want to ensure that we set for our script's duration only and the restore their previously set values upon script exit. The documentation describes these "specially treated variables": "They represent certain option settings that can be changed at run time by altering the value of the variable, or in some cases represent changeable state of psql. By convention, all specially treated variables' names consist of all upper-case ASCII letters (and possibly digits and underscores). To ensure maximum compatibility in the future, avoid using such variable names for your own purposes." Examples of these "specially treated variables" include AUTOCOMMIT, ECHO, ECHO_HIDDEN, PROMPT1, PROMPT2, PROMPT3, and VERBOSE, but there are many more.

For demonstration purposes, let's suppose you want to set the ECHO variable to something other than its default (none). For our purposes, we'll set ECHO to queries. We want to make sure, however, that we set it back to whatever it was when our script was called before leaving the script. The following simple psql logic accomplishes this.

\set PRIOR_ECHO :ECHO
\set ECHO queries

-- Run various queries for which you want to see the query itself output before the query results ...

\set ECHO :PRIOR_ECHO

It's as simple as that to temporarily set "specially treated variables" for your script's convenience without permanently changing the settings for the caller who might be running your script in the same psql session. The key things to remember are that the \set meta-command is always all lowercase, the specially treated variables have names that are always all uppercase but the specially treated variable values do not need to be uppercase (and typically are not), and the values in a variable can be accessed by prefixing the variable name with a colon (:).

PostgreSQL's psql \set versus SET

It is easy for someone who is new to PostgreSQL and who uses PostgreSQL's terminal editor psql to confuse the commands \set and SET. This post contrasts these commands and provides a brief overview of other commands that include the word "set".

The easiest way to remember how to differentiate \set from SET is to keep in mind that the "backslash commands" such as \set are "meta commands" for the command-line psql tool and do not mean anything to the PostgreSQL database itself. The SET command, which lacks a backslash, is a PostgreSQL database command that happens to be executed against the database from the psql command-line client.

Contrasting \set and SET
Command	\set	SET or set (or other case-insensitive variation1)
Context	psql terminal editor configuration meta command (interactive client terminal configuration)	PostgreSQL database configuration command (server configuration)
Ends with Semicolon?	No	Yes
Command Case Sensitive?	Yes (must be exactly \set)	No
Parameter/Variable Case Sensitive?	Yes2	No
How are Settings Displayed?	\set3	SHOW ALL; or show all;4
	\echo :variable_name	SHOW variable_name;5
Examples	\set AUTOCOMMIT on	SET search_path TO myschema, public;
Footnotes	I prefer to use all uppercase letters for SET to more clearly differentiate from /set. Two variables with same letters but different cases are two distinct variables. \set displays all variables when no arguments are provided to it. Any case variation (even sHoW aLl;) works. Any case variation of command and/or variable name also works.

There are two more psql meta commands that that "set" things and include the name "set". The \pset met command configures how psql presents "query result tables." Like \set, \pset can be specified without argument to see all of the current presentation settings.

Unlike the psql meta commands \set and \pset, the \gset psql metacommand does affect the PostgreSQL server because \gset submits the query buffer to the server and then stores the output returned from the server into specified psql variables. I discussed \gset with a few additional details in the blog post "Setting PostgreSQL psql Variable Based Upon Query Result."

Although \set and SET can be used to set variables, the easiest way to distinguish between them is to consider that the backslash commands such as \set are psql commands (and so \pset sets variables in the psql client tool) and commands without the backslash such as SET are PostgreSQL commands sent to the server from psql or from any other client (but ultimately set variables on the server).

jcmd, Circa JDK 11

Nicolas Fränkel recently published a survey of command-line tools delivered with OpenJDK 11 in the blog post "OpenJDK 11, tools of the trade." In that post, he briefly summarizes the tools jps (a JVM process status tool), jinfo (JVM configuration details), jmap (classes/objects on the heap), jstack (thread analysis), and graphical tool JConsole (monitor Java applications).

All of these tools are handy for Java developers to be aware of to apply as needed and Fränkel's post provides a nice introductory overview for those new to these tools. In recent years, I've moved toward applying the single jcmd tool instead of most of the other command-line tools (though it doesn't replace graphical tool JConsole in any way) as I've discussed in the post "jcmd: One JDK Command-Line Tool to Rule Them All."

There is a brief discussion on the related /r/java subreddit thread regarding jcmd versus the individual tools. I can see advantages to both approaches (using jcmd or using multiple individual tools). I contrast my perceptions of their relative advantages and disadvantages here.

jcmd Versus the Rest
jcmd	Other Tools
Single interactive tool	Different tools with varying names and options
More keystrokes/commands required to run functionality due to interactive nature	Fewer keystrokes required for those familiar with commands and options and for cases where command/options being used are supported for the given JVM process
jcmd <pid> help provides the specific functions supported on that JVM process for jcmd analysis	Results of running individual tool against JVM process is primary method of detecting that tool's support (or lack thereof) for that process
Supports only most commonly used subset of functionality of some of the individual tools	Each tool, by its nature, sets the bar for supported functionality
Newer with fewer online resources	Older with more online resources
Not considered "experimental"	Several of the individual tools (jps, jinfo, jmap, jstack, and more) are labeled "experimental" and are subject to change/removal (Tools Reference states that "experimental tools are unsupported and should be used with that understanding. They may not be available in future JDK versions. Some of these tools aren’t currently available on Windows platforms.")
Significant jcmd provided-details are available programmatically via DiagnosticCommandMBean	Direct corresponding programmatic access is rarely available for individual tools

Whether to use jcmd or one of the individual tools largely comes down to individual taste and preferences. Those who are already experienced with existing individual tools may prefer the more direct approach of those tools while those not familiar with the individual tools may prefer the interactive ability provided by jcmd for determining what tools and options are available. I certainly prefer non-experimental tools over "experimental" tools, but many of these tools have been labeled "experimental" for many versions of the JDK and are still with us.

The previously mentioned blog post "jcmd: One JDK Command-Line Tool to Rule Them All" describes how to use jcmd's interactive features to identify its capabilities supported for various JVM processes. There is a table toward the end of that post that "maps" jcmd options to some of the corresponding individual tools' commands and options. I reproduce that here for convenience.

Functionality	jcmd	Similar Tool
Listing Java Processes	jcmd	jps -lm
Heap Dumps	jcmd <pid> GC.heap_dump	jmap -dump <pid>
Heap Usage Histogram	jcmd <pid> GC.class_histogram	jmap -histo <pid>
Thread Dump	jcmd <pid> Thread.print	jstack <pid>
List System Properties	jcmd <pid> VM.system_properties	jinfo -sysprops <pid>
List VM Flags	jcmd <pid> VM.flags	jinfo -flags <pid>

The jcmd tool continues to be enhanced. JDK 9 saw several enhancements to jcmd via JEP 228 ("Add More Diagnostic Commands"). In JDK 11, support for displaying classloader hierarchies was added to jcmd. Here is a simple screen snapshot of that support for classloaders hierarchies in action.

As Fränkel concludes in his post, "The JDK offers a lot of out-of-box tools to help developers" and "they are a huge asset in a developer’s day-to-day job." This sentiment applies whether one chooses to use the individual JDK-provided tools or chooses to use jcmd.

Revealing the Queries Behind psql's Backslash Commands

PostgreSQL's psql interactive terminal tool provides several useful "backslash list commands" such as \d (lists "relations" such as tables, views, indexes, and sequences), \dt (lists tables), \di (lists indexes), \ds (lists sequences), \dv (lists views), \df (lists functions), \du (lists roles), and \? (displays help/usage details on backslash commands). These commands are concise and much simpler to use than writing the queries against PostgreSQL system catalogs (pg_class, pg_roles, pg_namespace, pg_trigger, pg_index, etc.) and information_schema that would provide the same types of details.

Although the psql backslash commands are easier to use than their associated queries, there are situations when it is important to know the full query behind a particular command. These situations include needing to perform a slightly different/adapted query from that associated with the pre-built command and needing to perform similar queries in scripts or code that are being used as PostgreSQL clients instead of psql. These situations make it important to be able to determine what queries psql is performing and the psql option -E (or --echo-hidden) allow that.

The PostgreSQL psql documentation states that the psql options -E and --echo-hidden "echo the actual queries generated by \d and other backslash commands." The documentation adds commentary on why this is useful, "You can use this to study psql's internal operations." When psql is started with the -E or --echo-hidden options, it will display the query associated with a backslash command before executing that command. The next screen snapshot illustrates this for the \du command used to show roles.

From use of psql -E and execution of the command \du, we're able to see that the query underlying \du is this:

Although the query is not nearly as nice to use as \du, we are now able to adapt this query for a related but different use case and are able to run this query from a PostgreSQL client other than psql.