|
Hi, I have killed the script, but still the query is showing in pg_stat and pg_locks. Please help me how to clear the pg_locks from the transaction it is already holding, if there is something in PostgreSQL which can clear the pg_stat and pg_locks? root@s3bgwa31 # ps -efa| grep -i 28223 postgres 28223 3114 0 Apr 22 ? 0:50 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data root 8670 8498 0 13:03:46 pts/3 0:00 grep -i 28223 root@s3bgwa31 # ps -efa| grep -i 3722 postgres 3722 3114 3 Apr 14 ? 21542:34 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data root 8675 8498 0 13:03:58 pts/3 0:00 grep -i 3722 root@s3bgwa31 # ptree 3722 3114 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data 3722 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data root@s3bgwa31 # ps -efa| grep -i 28223 root@s3bgwa31 # ps -ef| grep -i bgwcron root 8787 8498 0 13:06:45 pts/3 0:00 grep -i bgwcron root 3280 3278 0 Apr 14 ? 0:00 /bin/sh /opt/mediation/appl/SERVER/CXC1734739_R8Z/bin/BGwCronScript root 3278 298 0 Apr 14 ? 0:00 sh -c /opt/mediation/appl/SERVER/CXC1734739_R8Z/bin/BGwCronScript root@s3bgwa31 # kill -9 3280 3278 root@s3bgwa31 # ps -ef| grep -i bgwcron root 8813 8498 0 13:07:07 pts/3 0:00 grep -i bgwcron root@s3bgwa31 # psql -d fm_db_Server3 -p 5434 -U mmsupe Password for user mmsupe: psql: fe_sendauth: no password supplied root@s3bgwa31 # root@s3bgwa31 # psql -d fm_db_Server3 -p 5434 -U mmsuper Password for user mmsuper: psql (9.1.3) Type "help" for help. fm_db_Server3=# SELECT * FROM pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start
| qu ery_start | waiting | current_query -------+---------------+---------+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+-------------------------------+------------ -------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- 16384 | fm_db_Server3 | 28223 | 16391 | mmsuper | psql | | | -1 | 2015-04-22 11:39:12.384336+02 | 2015-04-22 11:39:33.36916+02
| 2015-04-22 11:39:33.36916+02 | t | delete from audittraillogentry where intime <= to_timestamp('2015-01-14 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3') OR outtime <= to_timestamp('2015-01-14
23:59:59.9 99', 'YYYY/MM/DD-HH24:MI:SS.FF3'); 16384 | fm_db_Server3 | 8822 | 16391 | mmsuper | psql | | | -1 | 2015-04-29 13:07:16.659052+02 | 2015-04-29 13:07:23.978483+02
| 2015-04-29 13:07:23.978483+02 | f | SELECT * FROM pg_stat_activity; 16384 | fm_db_Server3 | 3177 | 16391 | mmsuper | | 172.23.84.19 | s3bgwa31 | 53601 | 2015-04-14 13:29:50.170962+02 |
| 2015-04-29 04:00:05.991559+02 | f | <IDLE> 16384 | fm_db_Server3 | 3570 | 16391 | mmsuper | | 172.23.84.19 | s3bgwa31 | 53612 | 2015-04-14 13:32:38.494938+02 |
| 2015-04-14 13:32:38.506887+02 | f | <IDLE> 16384 | fm_db_Server3 | 3722 | 16391 | mmsuper | | 172.23.84.19 | s3bgwa31 | 53620 | 2015-04-14 13:36:29.193159+02 | 2015-04-14 13:36:29.204018+02
| 2015-04-14 13:36:29.204018+02 | f | delete from audittraillogentry where intime <= to_timestamp('2015-01-14 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3') OR outtime <= to_timestamp('2015-01-14
23:59:59.9 99', 'YYYY/MM/DD-HH24:MI:SS.FF3') (5 rows) 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/241919 | 8822 | AccessShareLock | t pg_index | relation | | 3/241919 | 8822 | AccessShareLock | t pg_namespace | relation | | 3/241919 | 8822 | 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=# \q root@s3bgwa31 # ps -ef| grep -i 3722 postgres 3722 3114 3 Apr 14 ? 21547:59 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data root 8901 8498 0 13:09:22 pts/3 0:00 grep -i 3722 root@s3bgwa31 # ps -ef| grep -i 28223 postgres 28223 3114 0 Apr 22 ? 0:50 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data root 8907 8498 0 13:09:30 pts/3 0:00 grep -i 28223 root@s3bgwa31 # ps -ef| grep -i 3114 postgres 3131 3114 0 Apr 14 ? 7:08 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 3129 3114 0 Apr 14 ? 7:07 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 3130 3114 0 Apr 14 ? 5:47 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 28223 3114 0 Apr 22 ? 0:50 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 3126 3114 0 Apr 14 ? 1:23 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 3132 3114 0 Apr 14 ? 14:16 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 3722 3114 3 Apr 14 ? 21548:20 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 3177 3114 0 Apr 14 ? 0:00 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data postgres 3114 1 0 Apr 14 ? 27:14 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data root 8914 8498 0 13:09:43 pts/3 0:00 grep -i 3114 postgres 3570 3114 0 Apr 14 ? 3:51 postmaster -D /var/opt/mediation/MMDB/fm_db_Server3/data root@s3bgwa31 # ps -ef| grep -i bgrcron root 8940 8498 0 13:10:07 pts/3 0:00 grep -i bgrcron root@s3bgwa31 # ps -ef| grep -i bgwcron root 8944 8498 0 13:10:10 pts/3 0:00 grep -i bgwcron root@s3bgwa31 # select count(*) from audittraillogentry ; root@s3bgwa31 # psql -d fm_db_Server3 -p 5434 -U mmsuper root@s3bgwa31 # ps -efa| grep -i delet root 8985 8498 0 13:11:18 pts/3 0:00 grep -i delet root@s3bgwa31 # From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]
On Behalf Of Mitu Verma 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 |
