Re: autovacuum and locks

Dietmar Maurer wrote:

> The pg_locks table shows the following:
>
> > SELECT pg_class.relname AS table, transaction, pid, mode, granted FROM
> pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid
> AND pg_locks.database = pg_database.oid;
>
> pg_class 3389057 | 26130 | AccessShareLock
> | t
> pg_class_oid_index 3389057 | 26130 | AccessShareLock
> | t
> pg_locks 3389057 | 26130 | AccessShareLock
> | t
>
>
> cgreylist 3368984 | 10979 |
> ShareUpdateExclusiveLock | t
> cgreylist_pkey 3368984 | 10979 |
> ShareUpdateExclusiveLock | t
> cgreylist_extime_index 3368984 | 10979 |
> ShareUpdateExclusiveLock | t
> cgreylist_instance_sender_index 3368984 | 10979 |
> ShareUpdateExclusiveLock | t
> cgreylist_mtime_index 3368984 | 10979 |
> ShareUpdateExclusiveLock | t
>
> cgreylist 3368998 | 10980 | ExclusiveLock
> | f
>
> cgreylist 3369000 | 10984 | AccessShareLock
> | t
> cgreylist 3369000 | 10984 | RowExclusiveLock
> | f # WHY?
> cgreylist_extime_index 3369000 | 10984 | AccessShareLock
> | t
>
> cgreylist 3388458 | 10023 | ExclusiveLock
> | f ?
> cgreylist 3388420 | 10021 | ExclusiveLock
> | f ?
>
>
> Why cant postgres get the RowExclusiveLock in transaction 3369000?

Probably because the ExclusiveLock'ers are waiting in front of
RowExclusiveLock. Locks are granted in order.

It would help if you didn't mangle the pg_locks output so badly.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"You're _really_ hosed if the person doing the hiring doesn't understand
relational systems: you end up with a whole raft of programmers, none of
whom has had a Date with the clue stick." (Andrew Sullivan)