Skip to content

PostgreSQL 16: GENERIC_PLAN option for EXPLAIN and SHELL exit codes for psql

Last week some smaller features got committed for PostgreSQL. The first one is an option which can be given to explain for displaying the generic plan for a statement. I’ve written about generic plans in the past, please read this before you continue and are not aware what this is about.

Displaying the help for EXPLAIN in psql will already show you the new option:

postgres=# \h explain
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    GENERIC_PLAN [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

URL: https://www.postgresql.org/docs/devel/sql-explain.html

Let’s create some sample data to have some to test with:

postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# insert into t1 select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000
postgres=# analyze t1;
ANALYZE

For seeing a generic before PostgreSQL 16 we would need to create a prepared statement and then execute that 6 times, or switch plan_cache_mode to ‘force_generic_plan’. Starting with PostgreSQL 16 we can simply do it like this:

postgres=# explain (generic_plan true) select b from t1 where a = $1;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Gather  (cost=1000.00..11613.43 rows=1 width=6)
   Workers Planned: 2
   ->  Parallel Seq Scan on t1  (cost=0.00..10613.33 rows=1 width=6)
         Filter: (a = $1)
(4 rows)

Nice, saves some work if you want to see the generic plan.

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)

The other feature went into psql. As you might know, you can easily execute commands on the operating system, as as long as the operating user who started psql has permissions to do so, e.g.:

postgres=# \! ls -l /var/tmp
total 8
drwx------ 3 root root 4096 Mar 27 11:05 systemd-private-42efbc3a4e614d01b5e49fb8a0b76627-systemd-logind.service-7C7X7o
drwx------ 3 root root 4096 Mar 27 11:05 systemd-private-42efbc3a4e614d01b5e49fb8a0b76627-systemd-timesyncd.service-5vVnaO

Until now there is no way to get the exit code from the shell command. You could try something like this:

postgres=$ \! ls -l /var/tmp && echo $?
total 8
drwx------ 3 root root 4096 Mar 27 11:05 systemd-private-42efbc3a4e614d01b5e49fb8a0b76627-systemd-logind.service-7C7X7o
drwx------ 3 root root 4096 Mar 27 11:05 systemd-private-42efbc3a4e614d01b5e49fb8a0b76627-systemd-timesyncd.service-5vVnaO
0
postgres=$ \! ls -l /var/tmps && echo $?
ls: cannot access '/var/tmps': No such file or directory
postgres=$ 

… but this will only give you the exit code for the successful command (but you don’t have it in psql yet). For the failed command it will not work at all.

Now you can do something like this:

postgres=$ \! ls -l /var/tmps
ls: cannot access '/var/tmps': No such file or directory
postgres=# select :'SHELL_ERROR';
 ?column? 
----------
 true
(1 row)

postgres=$ select :'SHELL_EXIT_CODE';
 ?column? 
----------
 2
(1 row)

postgres=$ 

Also nice, thanks to everyone involved.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *