Re: concurrent reindex issues

Tory M Blue <tmblue(at)gmail(dot)com> writes:
> *2009-10-07 22:18:02 PDT admissionclsdb postgres 10.13.200.70(46706) ERROR:
> deadlock detected*

> *2009-10-07 22:18:02 PDT admissionclsdb postgres 10.13.200.70(46706)
> DETAIL: Process 20939 waits for ShareLock on virtual transaction
> 16/43817381; blocked by process 1874.*

> * Process 1874 waits for ExclusiveLock on relation 17428 of database
> 16384; blocked by process 20939.*

> *2009-10-07 22:18:02 PDT admissionclsdb postgres 10.13.200.70(46706)
> STATEMENT: CREATE INDEX CONCURRENTLY prc_temp_idx_impressions_log_date2 ON
> tracking.impressions USING btree (log_date) TABLESPACE trackingindexspace*

Hmm. I suppose that 20939 was running the CREATE INDEX CONCURRENTLY,
and what it's trying to do with the ShareLock on a VXID is wait for some
other transaction to terminate so that it can safely complete the index
creation (because the index might be invalid from the point of view of
that other transaction). But the other transaction is waiting for
ExclusiveLock on what I assume is the table being indexed (did you check
what relation that OID is?).

AFAIK there are no built-in operations that take ExclusiveLock on user
tables, which means that 1874 would have had to be issuing an explicit
LOCK TABLE tracking.impressions IN EXCLUSIVE MODE
command. Perhaps that will help you track down what it was.

> So I'm at a lost, this first started happening in my slave DB (Slon
> replication), but it is now happening on my master which is odd.

I wouldn't be too surprised if the LOCK is coming from some Slony
operation or other. You might want to ask the slony hackers about it.

regards, tom lane