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):
# 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):
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:
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:
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):
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:
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:
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:
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
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:
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:
PGLOADBALANCEHOSTS
Franck Pachot for YugabyteDB ・ Jun 12 '23
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)
YugaByte seems really powerful !
Do you think it could be complicated for someone to start contributing ?
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:
docs.yugabyte.com/preview/contribute/
We have a #contributors channel in:
join.slack.com/t/yugabyte-db/share...