STV_RECENTS - Amazon Redshift


Use the STV_RECENTS table to find out information about the currently active and recently run queries against a database.

STV_RECENTS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
userid integer ID of user who generated entry.
status character(20) Query status. Valid values are Running, Done.
starttime timestamp Time that the query started.
duration integer Number of microseconds since the session started.
user_name character(50) User name who ran the process.
db_name character(50) Name of the database.
query character(600) Query text, up to 600 characters. Any additional characters are truncated.
pid integer Process ID for the session associated with the query, which is always -1 for queries that have completed.

Sample queries

To determine what queries are currently running against the database, type the following query:

select user_name, db_name, pid, query from stv_recents where status = 'Running';

The sample output below shows a single query running on the TICKIT database:

user_name | db_name | pid | query ----------+---------+---------+------------- dwuser | tickit | 19996 |select venuename, venueseats from venue where venueseats > 50000 order by venueseats desc;

The following example returns a list of queries (if any) that are running or waiting in queue to run:

select * from stv_recents where status<>'Done'; status | starttime | duration |user_name|db_name| query | pid -------+---------------------+----------+---------+-------+-----------+------ Running| 2010-04-21 16:11... | 281566454| dwuser |tickit | select ...| 23347

This query does not return results unless you are running a number of concurrent queries and some of those queries are in queue.

The following example extends the previous example. In this case, queries that are truly "in flight" (running, not waiting) are excluded from the result:

select * from stv_recents where status<>'Done' and pid not in (select pid from stv_inflight); ...