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.