View terminated queries with yb_pg_stat_get_queries

Use the YugabyteDB yb_pg_stat_get_queries function to see terminated queries and the reason for their termination.

When a query quits for unexpected reasons, information about the query and the responsible backend is stored. You can access this information by using yb_pg_stat_get_queries. Calling the function returns queries using the following criteria:

• Temporary file size exceeds temp_file_limit.
• Terminated by SIGSEGV - the query terminated due to a crash in the PostgreSQL process.
• Terminated by SIGKILL - the query was killed by the system's out of memory killer because the node is running out of memory.

The function takes a single argument, the Object identifier (OID) of the database of interest. If no OID is provided, it returns terminated queries for all databases.

Supported fields

At a ysqlsh prompt, run the following meta-commands to return the fields supported by yb_pg_stat_get_queries:

yugabyte=# \x
yugabyte=# \df yb_pg_stat_get_queries

List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------
Schema              | pg_catalog
Name                | yb_pg_stat_get_queries
Result data type    | SETOF record
Argument data types | db_oid oid, OUT db_oid oid, OUT backend_pid integer, 
                    | OUT query_text text, OUT termination_reason text, 
                    | OUT query_start timestamp with time zone, 
                    | OUT query_end timestamp with time zone
Type                | func

The following table describes the arguments and their values:

Examples

PostgreSQL crash

To simulate a crash in the PostgreSQL process, send a SIGSEGV signal to the backend process.

In a ysqlsh session, get the backend pid:

yugabyte=# SELECT pg_backend_pid();

 pg_backend_pid
----------------
           4650
(1 row)

In the same session, start a long-running query, so that you have time to send a signal while the query is running:

yugabyte=# SELECT * FROM generate_series(1, 123456789);

In another session, send the terminating signal to the backend process:

$ kill -SIGSEGV 4650 # the pid of the backend process

Verify that the query is listed as a terminated query as follows:

yugabyte=# SELECT backend_pid, query_text, termination_reason FROM yb_pg_stat_get_queries(NULL);

 backend_pid |                  query_text                  |  termination_reason
-------------+----------------------------------------------+-----------------------
        4650 | SELECT * FROM generate_series(1, 123456789); | Terminated by SIGSEGV
(1 row)

Exceed the temporary file limit

To simulate a query termination, set temp_file_limit to 0KB:

yugabyte=# SET temp_file_limit TO 0;

Now any query that requires a temporary file will result in an error.

To ensure failure, run a query that generates hundreds of millions of rows as follows:

yugabyte=# SELECT * FROM generate_series(1, 123456789);

ERROR:  temporary file size exceeds temp_file_limit (0kB)

To find the query in yb_pg_stat_get_queries, enter the following command:

yugabyte=# SELECT backend_pid, query_text, termination_reason FROM yb_pg_stat_get_queries(NULL);

 backend_pid |                  query_text                  |                termination_reason
-------------+----------------------------------------------+---------------------------------------------------
       23052 | SELECT * FROM generate_series(1, 123456789); | temporary file size exceeds temp_file_limit (0kB)
(1 row)

Out of memory

When a system is running critically low on memory, the out of memory killer will begin force killing processes. To simulate this, send a KILL signal to the backend process.

In a ysqlsh session, get the backend PID:

yugabyte=# SELECT pg_backend_pid();

 pg_backend_pid
----------------
           4801
(1 row)

In the same session, start a long-running query so that you have time to send a signal while the query is running:

yugabyte=# SELECT * FROM generate_series(1, 123456789);

In another session, send the terminating signal to the backend process:

$ kill -KILL 4801 # the pid of the backend process

Verify that the query is listed as a terminated query as follows:

yugabyte=# SELECT backend_pid, query_text, termination_reason FROM yb_pg_stat_get_queries(NULL);

 backend_pid |                  query_text                  |  termination_reason
-------------+----------------------------------------------+-----------------------
        4801 | SELECT * FROM generate_series(1, 123456789); | Terminated by SIGKILL
(1 row)

Return a query from another database

Create a terminated query by running the following command:

yugabyte=# SET temp_file_limit TO 0;
yugabyte=# SELECT 'db1' FROM generate_series(1, 123456789);

Create a second database and connect to it as follows:

yugabyte=# CREATE DATABASE new_db;
yugabyte=# \c new_db;

Create a second terminated query by running the following command:

new_db=# SET temp_file_limit TO 0;
new_db=# SELECT 'db2' FROM generate_series(1, 123456789);

Running yb_pg_stat_get_queries without providing an OID returns both queries:

new_db=# SELECT query_text FROM yb_pg_stat_get_queries(NULL);

                    query_text 
--------------------------------------------------
 SELECT 'db1' FROM generate_series(1, 123456789);
 SELECT 'db2' FROM generate_series(1, 123456789);
(2 rows)

When you run yb_pg_stat_get_queries with the OID of the current database, you only see the entries for that database.

To get the OIDs, enter the following command:

new_db=# SELECT oid, datname FROM pg_database;

  oid  |     datname
-------+-----------------
     1 | template1
 13285 | template0
 13286 | postgres
 13288 | yugabyte
 13289 | system_platform
 17920 | new_db
(7 rows)

Use the OID to get the terminated queries from the yugabyte database as follows:

new_db=# SELECT query_text FROM yb_pg_stat_get_queries(13288)

                    query_text 
--------------------------------------------------
 SELECT 'db1' FROM generate_series(1, 123456789);
(1 row)

Use the OID to get the terminated queries from the new database as follows:

new_db=# SELECT query_text FROM yb_pg_stat_get_queries(17920)

                    query_text 
--------------------------------------------------
 SELECT 'db2' FROM generate_series(1, 123456789);
(1 row)

Limitations

• The underlying data returned by the function is refreshed at 500 ms intervals, so if a recently terminated query is not listed, try querying again.
• The backend holds up to 1000 failed queries before it starts to overwrite the first queries.
• If the stat collector process is abruptly terminated, the underlying data may be corrupted and invalid.

Learn more

• For information on the temporary file limit, refer to temp_file_limit YB-TServer flag.
• Refer to Get query statistics using pg_stat_statements to track planning and execution of all the SQL statements.
• Refer to View live queries with pg_stat_activity to analyze live queries.