Re: how to release a transaction lock on a table?

On Tue, Feb 01, 2005 at 02:27:37PM -0800, Si Chen wrote:

> You are right. The transactions are idle--when I do a "ps auxw" on my
> database server, I see "....idle in transaction". Is this what you
> meant, and would the steps you talked about with pg_stat_activity help
> me track down the transactions that are idle?

Here's an example of what you'll get when stats_command_string is "on":

SELECT * FROM pg_stat_activity;

datid | datname | procpid | usesysid | usename | current_query | query_start
-------+---------+---------+----------+---------+---------------------------------------+-------------------------------
17232 | test | 26484 | 100 | mfuhr | <IDLE> in transaction | 2005-02-03 00:20:11.072507-07
17232 | test | 26482 | 100 | mfuhr | <IDLE> | 2005-02-03 00:19:24.445995-07
17232 | test | 26483 | 100 | mfuhr | ALTER TABLE foo ADD COLUMN name text; | 2005-02-03 00:20:36.089689-07
(3 rows)

> What's strange is that usually this does not cause problems. It is just
> occasionally that I am unable to gain access to a table. Unfortunately
> that also makes it hard to track down the source of the problem.

Joining pg_locks and pg_stat_activity can be useful. Here's an example:

SELECT p.procpid, p.usename, p.datname, l.relation::regclass,
l.granted, p.current_query, p.query_start
FROM pg_stat_activity AS p
JOIN pg_locks AS l ON l.pid = p.procpid
WHERE l.relation IN (SELECT relation FROM pg_locks WHERE granted IS FALSE)
ORDER BY l.relation;

procpid | usename | datname | relation | granted | current_query | query_start
---------+---------+---------+----------+---------+---------------------------------------+-------------------------------
26484 | mfuhr | test | foo | t | <IDLE> in transaction | 2005-02-03 00:20:11.072507-07
26483 | mfuhr | test | foo | f | ALTER TABLE foo ADD COLUMN name text; | 2005-02-03 00:20:36.089689-07
(2 rows)

If a connection holding a lock (granted = t) is idle, then you might
want to investigate what it's doing. It might simply have issued
a query, acquiring an AccessShareLock on the table, which conflicts
with the AccessExclusiveLock that ALTER TABLE wants. If that's
the case, then maybe the application could be modified to COMMIT
or ROLLBACK its transactions if it knows it's going to be idle for
a while.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/