Re: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

On Jan 3, 2008 5:12 PM, Mike White <mikewhite22(at)yahoo(dot)com> wrote:
> I'm performing a VACUUM FULL on a 7.4.7 db table, one that is not huge
> at any given time, but which has a lot of churn. Just the kind of
> usage pattern that 7.4 loves. :) This is something that we have to do
> every 6 months or so, and it always takes a long time.

I wonder why you have to do it every 6 months... Is this one of those
things where slowly the table bloats because fsm is too small or
vacuum is too infrequent? Or is this a case of some kind of
fragmentation? I've got plenty of heavily updated tables that USED to
live in a 7.4 and I never needed to run vacuum full on it, because
autovacuum (with slightly more aggressive settings than default) was
plenty on that db to keep the table clean. So I'm wondering if
there's some preventative maintenance that would stop you needing to
do this.

> Today, however,
> I've noticed that the pg_lock table shows two AccessExclusiveLock
> entries that are completely identical. Is this a sign of internal
> deadlock, or is this behavior expected during VACUUM FULL operation?

> Also, please don't tell me to upgrade.

OK. I will tell you to update. 7.4.7 had many data eating bugs that
were fixed by 7.4.18.

> We're happily running 8.2 on
> some production machines, but I am tied to 7.4 on this particular box
> for reasons beyond my control. :(

Been there, done that, got the t-shirt :-(

>
> <dbname>=# select * from pg_locks where mode = 'AccessExclusiveLock';
> relation | database | transaction | pid | mode |
> granted
> ------------+------------+-------------+-------+---------------------+---------
> 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
> t
> 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
> t

I'm guessing that the richer view that exists in 8.2 might show
differences, but the simpler view in 7.4 doesn't. You might try
adapting parts of the 8.2 view into 7.4 and seeing if that helps.