I am trying to find the lock sql queries, and I used this code,
But when I wanted to cancel the list of queries, it did not work.
Could you please help?
SELECT
*
FROM
stv_locks;
First off you shouldn't do this. Removing locks can cause loss of data and coherency issue. These issues can show up much later in time making repairing the data difficult or impossible. It is not advised to do this.
Second, why do you need to do this? What are you seeing that leads you to even want to do this? I've had customers want to do this because they are not using transactions properly. In short having long running sessions and not closing transactions can lead to a long list of possible locking issues.
OK on to trying to provide some information that might help. STV_LOCKS does not return all locks in the system. It is a view and just reports on write locks. Read locks are vastly more numerous in a MVCC database. To get these you will need to look at the PG_LOCKS table. However, I don't believe that you can just perform a SELECT * on this and will need a WHERE clause to get the query to run. An OID of the table in question is enough to explore the situation. When I need to track down read locks on a table I do something like:
select * from pg_locks where relation = (select oid from pg_class where relname = 'the_table');
But before you go off and start blowing away more locks I recommend you figure out what is going on. Once place to start is to look at all the sessions that are active ordered by start time.
SELECT * FROM STV_SESSIONS order by starttime;
If you have user sessions that are more than a day old you may want to ask why this is the case.
Or look for long running transactions in SVV_TRANSACTIONS (as superuser). If you are being impacted by long lived locks. More than likely your team just needs some education on ending their transactions.