Re: vacuum full problem

On Tue, 11 Nov 2003, pginfo wrote:

> The result by is:
> acc01=# select * from pg_locks;
> relation | database | transaction | pid | mode | granted
> ----------+----------+-------------+-------+-----------------+---------
> 16757 | 16976 | | 23169 | AccessShareLock | t
> 17062 | 16976 | | 1372 | AccessShareLock | t
> 17060 | 16976 | | 1372 | AccessShareLock | t
> | | 116303 | 1372 | ExclusiveLock | t
> | | 166846 | 23169 | ExclusiveLock | t
> 16995 | 16976 | | 1372 | AccessShareLock | t
> 17056 | 16976 | | 1372 | AccessShareLock | t
> 17038 | 16976 | | 1372 | AccessShareLock | t
> (8 rows)

Is that while the vacuum is running or just a general state that you might
run the vacuum in?

> And only transaction 116303 is the problem.
> Also it is production system with many transaction/sec and I am looking dor a
> way to detect what query is in this transaction ( if possible).
> The system is relativ big and it is dificult to detect this problem only from
> transacton number, but we will read the code to find the bug.

Well, you can turn on the statement statistics stuff and/or the
statement logging stuff to help you find what the various transactions
are doing. I'd wonder if you're maybe not closing a transaction after
it's completed its work though and so the locks are sitting around.