Tuesday, February 19, 2019

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 ECHO queries

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


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 (:).

Friday, February 15, 2019

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)
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;
  1. I prefer to use all uppercase letters for SET to more clearly differentiate from /set.
  2. Two variables with same letters but different cases are two distinct variables.
  3. \set displays all variables when no arguments are provided to it.
  4. Any case variation (even sHoW aLl;) works.
  5. 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).

Monday, February 4, 2019

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
jcmdOther 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.

FunctionalityjcmdSimilar 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.

Saturday, February 2, 2019

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 underling \du is this:

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'

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.