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

0
votes

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?

3
mysqlsqlamazon-redshiftpsqlsinglestore
psql is used with PostGreSQL, not MySQL. – Barmar
Thanks. I revised it to apply for both psql and mysql – code
can this put you in the direction? chrismiles.info/systemsadmin/databases/articles/… – Antony Gibbs
Thanks I guess the situation is that there can be many users making queries around the same time. I'd like to be able to uniquely identify the query id or query process id that runs the psql CLI query while it is running on 'this' local machine – code

3 Answers

1
votes

In mysql you can run select connection_id() to get the connection id of the current connection before running the query. You can then run kill <id> from another connection after starting the long running query.

1
votes

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
0
votes

For Redshift, it's select * from stv_recents where status='Running' and you'll be able to get pid from there