Re: Need some info on Postgresql - Mailing list pgsql-admin

On Jan 4, 2008 5:14 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Hi Scott,
>
> Thanks for your reply.
>
> I got the result for the query select * from pg_locks; as below. Can you
> explain me the output please.
>
> relation | database | transaction |  pid  |      mode       | granted
>
> ----------+----------+-------------+-------+-----------------+---------
>           |          |     7784829 | 21222 | ExclusiveLock   | t
>     87441 |    87343 |             | 21173 | AccessShareLock | t
>     87441 |    87343 |             | 21044 | AccessShareLock | t
>     87441 |    87343 |             | 21209 | AccessShareLock | t
>           |          |     7782935 | 20942 | ExclusiveLock   | t
>           |          |     7784034 | 21106 | ExclusiveLock   | t
>           |          |     7784091 | 21112 | ExclusiveLock   | t
>     87441 |    87343 |             | 21197 | AccessShareLock | t
>           |          |     7783246 | 20989 | ExclusiveLock   | t
>     87441 |    87343 |             | 21081 | AccessShareLock | t
>           |          |     7783542 | 21034 | ExclusiveLock   | t
>           |          |     7782957 | 20944 | ExclusiveLock   | t
>           |          |     7785128 | 21268 | ExclusiveLock   | t
>     87441 |    87343 |             | 21228 | AccessShareLock | t
>     87441 |    87343 |             | 20971 | AccessShareLock | t
>           |          |     7783524 | 21032 | ExclusiveLock   | t
>           |          |     7784237 | 21136 | ExclusiveLock   | t
>     87441 |    87343 |             | 21063 | AccessShareLock | t
>     87441 |    87343 |             | 20952 | AccessShareLock | t
>           |          |     7784975 | 21247 | ExclusiveLock   | t
>     87441 |    87343 |             | 21222 | AccessShareLock | t
>           |          |     7783940 | 21092 | ExclusiveLock   | t
>           |          |     7783596 | 21044 | ExclusiveLock   | t
>           |          |     7783670 | 21052 | ExclusiveLock   | t
>           |          |     7784165 | 21124 | ExclusiveLock   | t
>     87441 |    87343 |             | 21046 | AccessShareLock | t
>     87441 |    87343 |             | 21164 | AccessShareLock | t
>           |          |     7784793 | 21218 | ExclusiveLock   | t
>     87441 |    87343 |             | 21247 | AccessShareLock | t
>     87441 |    87343 |             | 21278 | AccessShareLock | t
>           |          |     7784393 | 21158 | ExclusiveLock   | t
>           |          |     7783363 | 21008 | ExclusiveLock   | t
>     87441 |    87343 |             | 21249 | AccessShareLock | t
>     87441 |    87343 |             | 21280 | AccessShareLock | t
>           |          |     7785374 | 21303 | ExclusiveLock   | t
>           |          |     7783707 | 21061 | ExclusiveLock   | t
>     87441 |    87343 |             | 21147 | AccessShareLock | t
>     87441 |    87343 |             | 21304 | AccessShareLock | t

Look up those relation numbers as oid against pg_class.  Note that I'm
running 8.2 and haven't run 7.4 seriously in over two years, so some
of this might not match up.  A query like this works in 8.2:

select pgc.relname, pgl2.relation from pg_locks pgl join pg_locks pgl2
on (pgl.relation is null AND pgl2.relation is not null and
pgl.pid=pgl2.pid) join pg_class pgc on (pgl2.relation=pgc.oid);

Like I said, no guarantees that will run on 7.4

What you're looking for is something holding an exclusive lock on the
table you're trying to reindex.  But it doesn't sound as if that table
is bloated to me, so I'm guessing something else is wrong.