Re: vacuum full problem - Mailing list pgsql-admin

From Stephan Szabo
Subject Re: vacuum full problem
Date
Msg-id 20031111093415.C65271@megazone.bigpanda.com
Whole thread Raw
In response to Re: vacuum full problem  (pginfo <pginfo@t1.unisoftbg.com>)
List pgsql-admin
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.


pgsql-admin by date:

Previous
From: pginfo
Date:
Subject: Re: vacuum full problem
Next
From: Danielle Cossette
Date:
Subject: Upgrading to Solaris 9