This page lists all queries in a single transaction.
The queries are ordered oldest top of table, most recent bottom of table; you scan down, like reading a page.
Remember that all queries execute in a transaction - really, internally, Redshift and Postgres operate much more on the basis of transactions than queries. Auto-commit transactions are transactions which, well, automatically open, issue one query, and then close, and so contain a single query only.
One important note; it is via the list of queries in a transaction that we can obtain the SQL behind a cursor.
Cursors can be used only inside transactions. The initial cursor query specifies the SQL, and then the later cursor queries pull rows from the output of the SQL. If you look through the query history on Redshift, you will see the later cursor queries, and they simply indicate a number (the number of rows to retrieve). They do not indicate the SQL.
However, all of the cursors commands, both the first and later, are in the same transaction.
What you do is find any of the cursor queries, go to the transaction it is in, list the queries in the transaction - and lo and behold, you will find the initial cursor query, which specifies the SQL.
| Name | Type |
|---|---|
| owner_user_id | int4 |
| owner | varchar |
| pid | int4 |
| xid | int8 |
| qid | int4 |
| query:state | varchar |
| query:start | timestamp |
| query:duration | interval |
| query:text | varchar |
| related pages | varchar |
The owner user ID. This column is emitted in CSV exports only.
The owner user name.
The process ID.
When a user connects to the cluster, a process is spawned, which accepts that connection. That’s the process the ID is for. That process is also the process which issues queries, and when you need to cancel a query, you in fact cancel the process, which by being cancelled, inherently also cancels the query it is running.
The transaction ID.
The query ID. This is really the WLM query ID; leader node queries have no query ID, at least, not that you see in the system tables. They do really have query IDs, of course, but they’re on the leader node only and they never show up in the system tables. All we see from the leader node are transaction IDs and process IDs.
The state of the query. This is one of the following;
| State | Description |
|---|---|
| aborted | Query (and so also transaction) aborted. |
| blocked | Transaction is blocked (by a lock held by another transaction). |
| completed | Query completed successfully. |
| disconnected | User session disconnected. |
| running | Query is active. |
For leader node queries, this is the time the query began executing.
For worker node queries, this currently is the time the query entered WLM.
As of 2023-01-08, this need improving - I should show all durations for queries (queue, compile, execute), here.
Query duration.
The query text, truncated at the 48 character mark.