Redshift Metadata tables: stv_locks, stv_inflight, stl_error, pg_locks, pg_class

Knowledge Drop

Last tested: Dec 3, 2017
 

... are tables that hold data about the database itself; tables, processes etc. DCLers commonly use these concurrently to troubleshoot hanging PDT queries or long running ones, etc.
 

stv_inflight & stv_recents stv_inflight show the queries currently running on the database (owned by the user):

SELECT * FROM stv_inflight

stv_recents will show the current AND recent queries running on the DB

SELECT * FROM stv_recents

and we can join in the stl_errors table to check for clues:

SELECT * FROM stv_recents JOIN stl_error on stv_recents.pid = stl_error.pid
 

stv_locks or pg_locks

SELECT * from stv_locks(or pg_locks) will return the locks on any tables in Redshift that are currently active. They will disappear as soon as the lock is released.

In order to see the table name you'll need to join in stv_table_perm or pg_class:

SELECT * FROM stv_locks JOIN stv_tbl_perm ON stv_locks.table_id = stv_tbl_perm.id

or

SELECT pg_class.relname, pg_locks.* FROM pg_locks LEFT OUTER JOIN pg_class ON pg_locks.relation = pg_class.oid

If you see locks on tables that a PDT is being built with, this is probably why the PDT query is hanging and backing up any query queues.

There are three steps to to PDT regen: CREATE, DROP and RENAME. We often have users that see that the Query has finished running in the database but is still building in Looker. Usually this means the CREATE step has completed but there is some lock preventing the DROP or RENAME step.

Usually locks happen when a table is being updated so generally waiting out the locks will clear up the issue.

For a good explanation of the difference between shared/exclusive locks: https://stackoverflow.com/questions/11837428/whats-the-difference-between-an-exclusive-lock-and-a-sh...

This content is subject to limited support.                

Version history
Last update:
‎05-07-2021 09:48 AM
Updated by: