YugabyteDB: list failed commands

This is an introduction to a new function in YSQL version 2.17. YSQL is YugabyteDB's enriched PostgreSQL version. The name of the function is yb_pg_stat_get_queries(). This new Yugabyte specific view lists failed (terminated) executions, for example (in ysqlsh):

Create a situation where an error results in terminated execution:

yugabyte=# set temp_file_limit to 0;
SET
yugabyte=# set work_mem to 64;
SET
yugabyte=# select * from generate_series(1,1000000);
ERROR:  temporary file size exceeds temp_file_limit (0kB)

Then query yb_pg_stat_get_queries():

yugabyte=# select * from yb_pg_stat_get_queries(null);
 db_oid | backend_pid |                query_text                 |                termination_reason                 |          query_start          |           query_end
--------+-------------+-------------------------------------------+---------------------------------------------------+-------------------------------+-------------------------------
  13288 |        3808 | select * from generate_series(1,1000000); | temporary file size exceeds temp_file_limit (0kB) | 2022-12-09 10:39:38.558119+00 | 2022-12-09 10:39:38.562892+00

The termination reason can also be (external) process termination, because the termination is registered by the the stats collector YSQL/PostgreSQL process. Obviously if the stats collector, or the parent of the stats collector (the postmaster) is terminated, the collected termination information is gone. For example a backend that was terminated using sudo kill -9 PID:

yugabyte=# select * from yb_pg_stat_get_queries(null);
 db_oid | backend_pid |                query_text                 |                termination_reason                 |          query_start          |           query_end
--------+-------------+-------------------------------------------+---------------------------------------------------+-------------------------------+-------------------------------
  13288 |        3808 | select * from generate_series(1,1000000); | temporary file size exceeds temp_file_limit (0kB) | 2022-12-09 10:39:38.558119+00 | 2022-12-09 10:39:38.562892+00
  13288 |        3808 | select pg_backend_pid();                  | Terminated by SIGKILL                             | 2022-12-09 10:43:15.044661+00 | 2022-12-09 10:43:30.604219+00

There is a nice technical detail here: if a PostgreSQL backend is terminated "externally", such as using a termination signal (kill command) or via the out of memory (OOM) killer, it will be detected and as a result the postmaster will restart all backend processes to avoid corruption.

As of version 2.17, YSQL does not perform this restarting. The reason we don't need to do this, is because any significant change to data is not done locally, but in our DocDB layer, which keeps consistency of any data change, which is outside of the backend process.