Re: How to prevent vacuum and reindex from deadlocking.

Robert Creager <Robert_Creager@LogicalChaos.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.

If you really want to rebuild only the one index, I think this will work:

begin;
lock table tab;
reindex index ndx;
commit;

I don't see a good system-level solution to this other than changing the
REINDEX syntax to include the table name (cf. CLUSTER).

            regards, tom lane