DEV Community

Franck Pachot for YugabyteDB

Posted on • Updated on

connecting with psql 16 beta to YugabyteDB and use the most recent features

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

sudo dnf remove postgresql
cd /var/tmp
wget -c${v}/postgresql-${v}.tar.gz
tar -zxvf postgresql-${v}.tar.gz
sudo dnf install -y libicu-devel openssl-devel readline-devel
cd /var/tmp/postgresql-${v}
./configure --with-openssl
sudo make install
Enter fullscreen mode Exit fullscreen mode

With this, psql connects and shows the versions of the client (16beta1) and server (YugabyteDB 2.18 with PostgreSQL 11.2 compatibility):

Pager usage is off.
psql (16beta1, server 11.2-YB-
Type "help" for help.

Enter fullscreen mode Exit fullscreen mode

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:

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)
Enter fullscreen mode Exit fullscreen mode

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:

yugabyte=# select query from pg_stat_statements;
 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
(10 rows)

Enter fullscreen mode Exit fullscreen mode

%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):

yugabyte=# begin transaction;
yugabyte=*# select 0/0;
ERROR:  division by zero
yugabyte=!# rollback;
Enter fullscreen mode Exit fullscreen mode

\dconfig with wildcard

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

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)
Enter fullscreen mode Exit fullscreen mode

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:

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

Enter fullscreen mode Exit fullscreen mode

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:

yugabyte=# set yb_debug_report_error_stacktrace to on;
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)
Enter fullscreen mode Exit fullscreen mode

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

yugabyte=# \pset format csv
Output format is csv.
yugabyte=# select * from pg_am;
Enter fullscreen mode Exit fullscreen mode

get error code from last \!

When running shell commands with \! is is good to test the return code. It goes to specific variables:

yugabyte=# \! exit 42
yugabyte=# select case when :SHELL_ERROR then :SHELL_EXIT_CODE end error;
Enter fullscreen mode Exit fullscreen mode

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:

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.

Top comments (2)

yet_anotherdev profile image
Lucas Barret • Edited

YugaByte seems really powerful !
Do you think it could be complicated for someone to start contributing ?

franckpachot profile image
Franck Pachot

It depends on the level of contribution. Testing any app that works on postgres and creating issues if anything works differently is already a great contribution. For contributions to the code, it really depends on which parts. The distributed storage & transactions is C++ and not always easy to understand (from my side... may be easy for others!)
Our contribution guide is short:
We have a #contributors channel in: