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.

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.

Post Views: 1,487
Thumbnail [60x60]
by
Daniel Westermann