Re: vacuum full problem

On Tue, 11 Nov 2003, pginfo wrote:

> 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

Not necessarily. People are going to be able to insert/update/delete from
the tables (the locks are AccessShareLock) because those don't get a
conflicting table lock. They're not going to be able to do things like
vacuum full or alter table however because those do.

> How big is the penalty for statistic on?

I'm not sure, but you can turn it on until you find it and then turn it
off again.