Re: How to prevent vacuum and reindex from deadlocking.

On Mon, 11 Aug 2003 11:05:57 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> said something like:

> Robert Creager <Robert_Creager(at)LogicalChaos(dot)org> writes:
> > So, my guess is that 18735 is the vacuum process (likely vacuum analyze,
> > driven from pg_autovacuum), and 188735 is a 'REINDEX INDEX
> > temp_obs_i_loc_index'.
>
> Can you use a "REINDEX TABLE" instead? REINDEX INDEX is problematic
> since it first finds/locks the index and then has to find/lock the
> table. Everything else (except perhaps DROP INDEX) goes the other way.

I'll try. There are other indexes on the table. I'll see what the performance
does.

>
> If you really want to rebuild only the one index, I think this will work:
>
> begin;
> lock table tab;
> reindex index ndx;
> commit;

Duh. I quickly glanced at the docs, and saw the warning on the nested
transaction, and thought that wasn't supported. I remember that it is
supported now...

Cheers,
Rob

--
09:38:17 up 10 days, 2:23, 4 users, load average: 2.13, 2.90, 3.18