Re: clearing of the transactions shown in pg_locks

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 transactions ongoing 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 run the delete command on the table?

Regards
Mitu

-----Original Message-----
From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
Sent: April 29, 2015 6:40 PM
To: Mitu Verma; 'pgsql-general(at)postgresql(dot)org' (pgsql-general(at)postgresql(dot)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