Re: vacuum full problem - Mailing list pgsql-admin

Stephan Szabo wrote:

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

Can you point me to any place in docs to read more detailed about locks and statistic (
I have idea, butt also I will to know more if possible).

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

  regards,
ivan.