如何通过Redshift系统表排查终止查询的原因？

Great question! You absolutely can trace terminated queries in stl_query to their root error causes using Redshift's system tables—even when stl_errors uses process IDs instead of query IDs. Let me walk you through the most reliable methods to bridge that gap:

1. Start with the most direct approach: stl_query + stl_query_error #

Redshift has a dedicated table for query-level errors: stl_query_error. This table links directly to stl_query via the queryid field, so you don’t have to mess with PID mappings at all. This should be your first stop because it’s the most straightforward and reliable.

Here’s a sample query to pull terminated queries and their errors:

SELECT
  q.queryid,
  q.query,
  q.starttime,
  q.endtime,
  qe.error,
  qe.sqlstate,
  q.reason  -- Quick summary of why the query was terminated
FROM stl_query q
LEFT JOIN stl_query_error qe ON q.queryid = qe.queryid
WHERE q.status = 'Aborted'
ORDER BY q.endtime DESC;

This will give you:

• The query ID and full SQL text of the terminated query
• Detailed error messages and SQL state codes (if available)
• A short reason for termination right in stl_query.reason (think: "Query cancelled by user", "WLM query slot timeout", or "Insufficient memory")

2. Fall back to stl_query + stl_errors for low-level process errors #

If you run into errors that don’t show up in stl_query_error (rare, but possible for low-level process-related issues), you can connect the two tables using the process ID (pid) and a time range filter. Since PIDs can be reused over time, adding a time check ensures you’re linking the right error to the right query.

Try this query:

SELECT
  q.queryid,
  q.query,
  q.starttime,
  q.endtime,
  e.error,
  e.context,
  e.sqlstate,
  q.reason
FROM stl_query q
JOIN stl_errors e 
  ON q.pid = e.pid
  -- Ensure the error occurred while the query was running
  AND e.time BETWEEN q.starttime AND q.endtime
WHERE q.status = 'Aborted'
ORDER BY q.endtime DESC;

Pro Tips #

• Check stl_query.reason first: A lot of common termination reasons are already spelled out here—you might not even need to join error tables to get the answer you need.
• Always use time filters: Redshift reuses PIDs, so skipping the time range could lead to incorrect associations between old errors and new queries.
• Permissions matter: You’ll need SUPERUSER access or explicit SELECT permissions on these system tables to run these queries.

内容的提问来源于stack exchange，提问作者Sayed Awesh Rahman