How to get query ID after executing psql/mysql CLI command line?

Question

I am using psql CLI command line to make a query to Redshift. (question applies to mysql also). The query may take a long time to execute. After sending the command, is it possible to get the Query ID of that query while it is running without waiting until it is finished. Is this possible?

For example, if I need to abort that query hours/days later, I'd like to send a CANCEL/ABORT command to the Redshift backend for that specific query. Note: the same database will be used by others, so I'd like to know the ID of the specific query I executed.

Also, let's say the psql command is running in a shell. Is it possible to programmatically (python) in an app to get the Query ID from that psql CLI process?

Answer 1

In Amazon Redshift, there are a couple of ways to determine the running queries -

STV_INFLIGHT - shows only currently running queries (http://docs.aws.amazon.com/redshift/latest/dg/r_STV_INFLIGHT.html)

select userid, usename, query, pid, starttime, "text", 
suspended from stv_inflight s, pg_user u 
where s.userid = u.usesysid

STV_RECENTS - shows currently running and recently completed queries with status (http://docs.aws.amazon.com/redshift/latest/dg/r_STV_RECENTS.html)

select * from stv_recents;

Now, to abort a running query, you need the pid (Process ID) of the currently running queries. You can then terminate the query using one of the below commands -

select pg_cancel_backend(<pid>); OR
select pg_terminate_backend(<pid>) ; OR
cancel <pid>;

query#2

select userid, usename, query, s.pid, c.remotehost, c.application_name, 
starttime, "text", 
suspended from stv_inflight s, stl_connection_log c,  pg_user u 
where s.userid = u.usesysid and s.pid = c.pid