How do I get Amazon Redshift session details

Please check my suggestion below:

In Amazon Redshift, certain system tables and views provide session-related information, but the level of detail might not be the same as in PostgreSQL. The behavior you're encountering with the pg_stat_activity query is due to the differences between Redshift and PostgreSQL. Amazon Redshift is optimized for analytical workloads and has its own inter_nal architecture that diverges from PostgreSQL in various ways.

I believe Amazon Redshift doesn't expose the current_query column in the same way that PostgreSQL does, and you're correct that stats_command_string is not a parameter that you can modify in Redshift.

If you want to get session details in Amazon Redshift, you can consider using the stl_wlm_query system table and the stl_connection_log system table, which might provide some useful information about active and historical queries. However, these tables might not provide the exact same level of detail that you're used to in PostgreSQL's pg_stat_activity.

Here's a query that you could use to retrieve session details from Redshift's stl_wlm_query table:

SELECT
  query,
  userid,
  starttime,
  endtime,
  label,
  status
FROM stl_wlm_query;

Please note that Amazon Redshift's features and behavior might have evolved since my last update, so I recommend referring to the latest Amazon Redshift documentation or AWS forums for the most accurate and up-to-date information on querying session details in Redshift.

Amazon Redshift is based on PostgreSQL, but it has been heavily modified by Amazon to support distributed processing and other features. As a result, not all PostgreSQL features and system views are available or behave the same way in Redshift.

In Redshift, the pg_stat_activity view does not provide the current query being executed by each session, as you've noticed. The stats_command_string parameter is also not modifiable in Redshift, which is why you're seeing the permission denied error.

However, you can get session details from a combination of system views. Here's how you can retrieve session details in Redshift:

stv_sessions: This view provides information about user sessions. It includes user ID, start time, and other session-related details.

stv_recents: This view provides information about the most recent queries that have been run. It includes the query string, the user who ran it, and other query-related details.

To get session details similar to pg_stat_activity, you can join these views. Here's a sample query:

SELECT
    s.pid,
    s.user_name,
    s.starttime,
    r.query,
    r.status
FROM
    stv_sessions s
LEFT JOIN
    stv_recents r ON s.pid = r.pid
WHERE
    r.query NOT LIKE 'COMMIT'
AND
    r.query NOT LIKE 'BEGIN';

This query will give you the session ID (pid), the user name, the session start time, the most recent query executed by that session, and the status of that query.

Note: The stv_recents view only shows the most recent queries, so if a session has been idle for a while, its most recent query might not appear in this view. Also, the stv_recents view includes BEGIN and COMMIT statements, which you might want to filter out, as shown in the sample query above.

I hope this helps you get the session details you need from Redshift!