connecting with psql 16 beta to YugabyteDB and use the most recent features
Tags: postgres, yugabytedb, distributed, sql
Published: 2023-06-01 10:47:22

YugabyteDB is shipped with `ysqlsh` which is the fork of `psql` with a few modifications to provide the additional features syntax and descriptions. Currently (version 2.18) the PostgreSQL fork compatibility is PG11. However, because YugabyteDB is PostgreSQL compatible, you can also connect with the PostgreSQL `psql` and even new versions. Here, I'm showing how to compile the latest beta (PG16beta) and show a few interesting features that came to `psql` since version 11.2

## Build the binaries

Here is what I run to install PostgreSQL 16 beta 1 on my lab environment (Oracle Linux 8 on an Oracle Cloud free tier instance):
```sh
# v=16beta1 -- or get the latest version and be lucky:
v=$(curl -sL https://ftp.postgresql.org/pub/source | awk -F '"' '/<a href="/{print gensub("(^v|/$)","","g",$2)}' | sort -rV | head -1)
sudo dnf remove postgresql
cd /var/tmp
wget -c https://ftp.postgresql.org/pub/source/v${v}/postgresql-${v}.tar.gz
tar -zxvf postgresql-${v}.tar.gz
sudo dnf install -y libicu-devel openssl-devel readline-devel make gcc zlib-devel
cd /var/tmp/postgresql-${v}
./configure --with-openssl --enable-debug CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer"
make
sudo make install
```

With this, `psql` connects and shows the versions of the client (16beta1) and server (YugabyteDB 2.18 with PostgreSQL 11.2 compatibility):
```sql
Pager usage is off.
psql (16beta1, server 11.2-YB-2.18.0.1-b0)
Type "help" for help.

yugabyte=#
```

## number of executions for `\watch`

The new feature that I'll use the most is the possibility to run `watch` only for a specific number of time. I use this to fill test tables with generated data. Here is an example running the last command 3 times every 1 second:

```sql
yugabyte=# select now(), pg_sleep(0.9);
              now              | pg_sleep
-------------------------------+----------
 2023-06-01 10:29:56.949445+00 |
(1 row)

yugabyte=# \watch i=1 c=3
Thu 01 Jun 2023 10:30:02 AM GMT (every 1s)

              now              | pg_sleep
-------------------------------+----------
 2023-06-01 10:30:02.713929+00 |
(1 row)

Thu 01 Jun 2023 10:30:03 AM GMT (every 1s)

              now              | pg_sleep
-------------------------------+----------
 2023-06-01 10:30:03.713898+00 |
(1 row)

Thu 01 Jun 2023 10:30:04 AM GMT (every 1s)

             now              | pg_sleep
------------------------------+----------
 2023-06-01 10:30:04.71389+00 |
(1 row)
```

## extended protocol with `\bind \g`

You may want to test queries with parameters, either be simulate the same as what the application does, or simply because you copy/paste it from what was collected by pg_stat_statements. This is now possible by running `\bind` before `g`:

```sql
yugabyte=# select query from pg_stat_statements;
                            query
--------------------------------------------------------------
 select relname from pg_class where relname like $1 escape $2

yugabyte=# select relname from pg_class where relname like $1 escape '\'

yugabyte-# \bind 'pg_yb_%' \g
              relname
------------------------------------
 pg_yb_catalog_version
 pg_yb_catalog_version_db_oid_index
 pg_yb_migration
 pg_yb_profile
 pg_yb_profile_oid_index
 pg_yb_profile_prfname_index
 pg_yb_role_profile
 pg_yb_role_profile_oid_index
 pg_yb_tablegroup
 pg_yb_tablegroup_oid_index
(10 rows)

```

## `%x` in the default prompt

The default prompt since PG13 is `%/%R%x%# ` instead of `%/%R%# ` and the shows the state of the transaction (`*` for ongoing transaction, `!` when failed):
```sql
yugabyte=# begin transaction;
BEGIN
yugabyte=*# select 0/0;
ERROR:  division by zero
yugabyte=!# rollback;
ROLLBACK
```

## `\dconfig` with wildcard

You may be used to find your parameters with `show` and autocompletion. `\dconfig` now can take wildcards, which makes it easier:

```sql
yugabyte=# \dconfig yb*pushdown*
   List of configuration parameters
           Parameter           | Value
-------------------------------+-------
 yb_enable_expression_pushdown | on
 yb_enable_sequence_pushdown   | on
 yb_pushdown_strict_inequality | on
(3 rows)
```

## Show SQLSTATE instead of error message

It is interesting to know the error code (SQLSTATE) because this is what you will test in your application. This is used to know if it is an error to retry for example. The new `\set VERBOSITY sqlstate` shows only the SQLSTATE without the error message, which makes it easier to get reproducible output:

```sql
yugabyte=# \set VERBOSITY default
yugabyte=# select 0/0;
ERROR:  division by zero

yugabyte=# \set VERBOSITY verbose
yugabyte=# select 0/0;
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:820

yugabyte=# \set VERBOSITY sqlstate
yugabyte=# select 0/0;
ERROR:  22012

```

I used to set `\set VERBOSITY verbose` to get the SQLSTATE, the error message, and the place in the code if there's a need to understand better the reason. Note that in YugabyteDB you can even get the stack trace:
```sql
yugabyte=# set yb_debug_report_error_stacktrace to on;
SET
yugabyte=# select 0/0;
ERROR:  division by zero
    @     0xaaade360f018  errmsg
    @     0xaaade34e61e0  int4div
    @     0xaaade3207944  ExecInterpExpr
    @     0xaaade334a264  evaluate_expr
    @     0xaaade3349a7c  simplify_function
    @     0xaaade3348d14  eval_const_expressions_mutator
    @     0xaaade3296de8  expression_tree_mutator
    @     0xaaade3296ec0  expression_tree_mutator
    @     0xaaade3325280  subquery_planner
    @     0xaaade332470c  standard_planner
    @     0xfffbeb517ce8  (unknown)
    @     0xaaade3454e80  pg_plan_queries
    @     0xaaade34602c0  yb_exec_simple_query_impl
    @     0xaaade34611b8  yb_exec_query_wrapper_one_attempt
    @     0xaaade345b664  PostgresMain
    @     0xaaade3394d7c  BackendRun
    @     0xaaade33940f4  ServerLoop
    @     0xaaade338f76c  PostmasterMain
    @     0xaaade3293aec  PostgresServerProcessMain
    @     0xaaade2f6fe78  main
    @     0xfffbf0534b2c  __libc_start_main
    @     0xaaade2f6fd34  (unknown)
```

## CSV output with `--csv` or `\pset format csv`

Generating CSV can be done with COPY but it is also possible to do in `psql` with a simple `format`
```sql
yugabyte=# \pset format csv
Output format is csv.
yugabyte=# select * from pg_am;
amname,amhandler,amtype
btree,bthandler,i
hash,hashhandler,i
gist,gisthandler,i
gin,ginhandler,i
brin,brinhandler,i
spgist,spghandler,i
ybgin,ybginhandler,i
lsm,ybcinhandler,i
```

## get error code from last `\!`

When running shell commands with `\!` is is good to test the return code. It goes to specific variables:
```sql
yugabyte=# \! exit 42
yugabyte=# select case when :SHELL_ERROR then :SHELL_EXIT_CODE end error;
error
42
```

## Poor-man client side load-balancing

Another feature can be useful to balance the connections when there is no other solution (YugabyteDB smart drivers, or HA proxy). This is detailed in the next post:
{% embed https://dev.to/yugabyte/pgloadbalancehosts-1be6 %}

Remember that using the beta client is not for production, even if those features have very low chances to corrupt the data or the result. However, using a generally available client that is more recent than the server is supported, so you are not stick to 11.2 when connecting to YugabyteDB.