|
Hi, I am new to postgreSQL and facing an issue with the transactions which are being shown in pg_locks. We are using a script which deletes entries from a table called audottrailLogEntry table. This script first does the indexing then it deletes the entries from the table. Now issue is that this script is taking lot of time and has acquired some locks
also. Nor sure if it is happening due to indexing or what. We did not stop the script and after that when we tried to manually run the delete operaion, that query has also acquired some locks and is not working.
1.
We can stop the script which is running in background , but if stopping the script is sufficient to release the locks which are shown in pg_locks? 2.
We want to kill the script and manally want to run the delete operation, what steps should I follow besides killing the script, which will clear all
the transactions held by the previously running script? fm_db_Server3=# select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc; relname | locktype | page | virtualtransaction | pid | mode | granted --------------------+----------+--------+--------------------+-------+------------------+--------- pg_class | relation | | 3/31423 | 10675 | AccessShareLock | t pg_index | relation | | 3/31423 | 10675 | AccessShareLock | t pg_namespace | relation | | 3/31423 | 10675 | AccessShareLock | t audittraillogentry | relation | | 2/33089 | 28223 | RowExclusiveLock | t audittraillogentry | relation | | 6/94 | 3722 | RowExclusiveLock | t audittraillogentry | tuple | 872812 | 2/33089 | 28223 | ExclusiveLock | t audittraillogentry | relation | | 6/94 | 3722 | RowShareLock | t cdrdetails | relation | | 6/94 | 3722 | RowExclusiveLock | t cdrlogentry | relation | | 6/94 | 3722 | RowShareLock | t (9 rows) fm_db_Server3=# SELECT * FROM pg_locks; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted ---------------+----------+----------+--------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+--------- virtualxid | | | | | 2/33089 | | | | | 2/33089 | 28223 | ExclusiveLock | t relation | 16384 | 16409 | | | | | | | | 6/94 | 3722 | AccessShareLock | t relation | 16384 | 16409 | | | | | | | | 6/94 | 3722 | RowExclusiveLock | t virtualxid | | | | | 3/31424 | | | | | 3/31424 | 10675 | ExclusiveLock | t relation | 16384 | 16406 | | | | | | | | 6/94 | 3722 | RowShareLock | t relation | 16384 | 16406 | | | | | | | | 6/94 | 3722 | RowExclusiveLock | t transactionid | | | | | | 166393 | | | | 6/94 | 3722 | ExclusiveLock | t relation | 16384 | 50024 | | | | | | | | 6/94 | 3722 | AccessShareLock | t relation | 16384 | 50024 | | | | | | | | 6/94 | 3722 | RowExclusiveLock | t virtualxid | | | | | 6/94 | | | | | 6/94 | 3722 | ExclusiveLock | t relation | 16384 | 11000 | | | | | | | | 3/31424 | 10675 | AccessShareLock | t relation | 16384 | 16479 | | | | | | | | 6/94 | 3722 | AccessShareLock | t relation | 16384 | 50026 | | | | | | | | 2/33089 | 28223 | RowExclusiveLock | t relation | 16384 | 50025 | | | | | | | | 2/33089 | 28223 | RowExclusiveLock | t relation | 16384 | 50027 | | | | | | | | 2/33089 | 28223 | RowExclusiveLock | t relation | 16384 | 16420 | | | | | | | | 6/94 | 3722 | RowExclusiveLock | t relation | 16384 | 50026 | | | | | | | | 6/94 | 3722 | AccessShareLock | t relation | 16384 | 50026 | | | | | | | | 6/94 | 3722 | RowExclusiveLock | t relation | 16384 | 50025 | | | | | | | | 6/94 | 3722 | AccessShareLock | t relation | 16384 | 50025 | | | | | | | | 6/94 | 3722 | RowExclusiveLock | t relation | 16384 | 50027 | | | | | | | | 6/94 | 3722 | AccessShareLock | t relation | 16384 | 50027 | | | | | | | | 6/94 | 3722 | RowExclusiveLock | t tuple | 16384 | 16406 | 872812 | 52 | | | | | | 2/33089 | 28223 | ExclusiveLock | t transactionid | | | | | | 166395 | | | | 2/33089 | 28223 | ExclusiveLock | t transactionid | | | | | | 166393 | | | | 2/33089 | 28223 | ShareLock | f relation | 16384 | 50024 | | | | | | | | 2/33089 | 28223 | RowExclusiveLock | t relation | 16384 | 16438 | | | | | | | | 6/94 | 3722 | RowShareLock | t relation | 16384 | 16409 | | | | | | | | 2/33089 | 28223 | RowExclusiveLock | t relation | 16384 | 16406 | | | | | | | | 2/33089 | 28223 | RowExclusiveLock | t (29 rows) fm_db_Server3=# fm_db_Server3=# select count(*) from audittraillogentry ; count --------- 8872597 (1 row) Regards Mitu |
