Re: vacuum full problem

Hi,

Stephan Szabo wrote:

> 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?
>

It looks as general state.

> > 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.

I will do it.
It is possible to be one not closed transaction, but in this case nobody will be
able to modify this table (tables) and
the system will stop to respond. The paradox is that the system works well without
any problems and on this basis I wrote that (at the beginning) that the problem is
by vacuum.
How big is the penalty for statistic on?

Generaly I think we will be able to found the problem quick.

regards,
ivan