Обсуждение: clearing of the transactions shown in pg_locks

Поиск
Список
Период
Сортировка

clearing of the transactions shown in pg_locks

От
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

Re: clearing of the transactions shown in pg_locks

От
Mitu Verma
Дата:

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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mitu Verma
Sent: April 29, 2015 11:02 AM
To: 'pgsql-general@postgresql.org' (pgsql-general@postgresql.org)
Subject: [GENERAL] clearing of the transactions shown in pg_locks

 

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

Re: clearing of the transactions shown in pg_locks

От
Albe Laurenz
Дата:
Mitu Verma wrote:
> 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?

[...]

> fm_db_Server3=# SELECT * FROM pg_stat_activity;

[...]

> 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 |    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')

You killed the script, but you did not cancel the queries.

Maybe you should change the script so that it cancels the queries when it is killed.

To get rid of the database sessions, do the following as superuser (because you are on 9.1):

SELECT pg_terminate_backend(28223);
SELECT pg_terminate_backend(3722);

Yours,
Laurenz Albe

Re: clearing of the transactions shown in pg_locks

От
Jim Nasby
Дата:
On 4/29/15 12:32 AM, Mitu Verma wrote:
> 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.

Mixing DDL with bulk DML operations is not a recipe for good
concurrency. For that matter, running DDL on a recurring basis isn't
good either.

BTW, you might want to look at using PgQ to do the queuing; it has
different methods of maintaining it's queues so you don't run into these
sort of problems.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Re: clearing of the transactions shown in pg_locks

От
Mitu Verma
Дата:
Thanks Albe .
It worked. But now there is one more issue which I am facing regarding this.
I performed the following steps -

1. I stopped the script which is doing some operation( delete etc) in one of the tables.

2. SELECT pg_terminate_backend(proc pid). It has terminated the database sessions and now I can see that there are no
transactionsongoing in this table in pg_locks or pg_stats.
 

3.Now when I am trying to run the manual delete (i.e not using the script) using this command 
delete from audittraillogentry where intime <= to_timestamp('2015-01-30 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3') OR
outtime <= to_timestamp('2015-01-30 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3');
 

Then that query is hung and even after 5 hours not a single row has been deleted.

 Probably earlier script is still causing some issue, not sure. How can I completely refresh the database session and
runthe  delete command on the table?
 

Regards
Mitu

-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] 
Sent: April 29, 2015 6:40 PM
To: Mitu Verma; 'pgsql-general@postgresql.org' (pgsql-general@postgresql.org)
Subject: RE: [GENERAL] clearing of the transactions shown in pg_locks

Mitu Verma wrote:
> 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?

[...]

> fm_db_Server3=# SELECT * FROM pg_stat_activity;

[...]

> 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 |    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')

You killed the script, but you did not cancel the queries.

Maybe you should change the script so that it cancels the queries when it is killed.

To get rid of the database sessions, do the following as superuser (because you are on 9.1):

SELECT pg_terminate_backend(28223);
SELECT pg_terminate_backend(3722);

Yours,
Laurenz Albe