Обсуждение: concurrent reindex issues

Hey all, it's been a bit however I'm running into some issues with my concurrent index

Always get this error during a concurrent index.

 

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

 

This happens all the time, so it's not the occasional deadlock. We even turned off all applications that insert into the database and it still fails.

Tried restarting the database as well.

Also when looking at active connections there is no process 1874.

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.

Any idea?

postgres 8.3.4

Linux system.

 

Tory M Blue <tmblue@gmail.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

On Thu, Oct 8, 2009 at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

I've had issues like this.  Shutting down the slon daemons before
running such commands would usually allow them to finish, at the cost
of replication falling behind while it runs.

More update

If I run the concurrent re index locally (psql session) it works fine, but when run via a connection through php I get the error

Can't be slon, since I can do this locally, but why would postgres have an issue with a remote connection?

the basic script:
  $connectString = "host=server dbname=clsdb user=postgres password=password";
        $dbconn = pg_connect($connectString);
        if (!$dbconn) {
                print "Could not connect";
                die();
        }
          $result = pg_query( $dbconn, "CREATE INDEX CONCURRENTLY  prc_temp_idx_impressions_log_date ON tracking.impressions USING btree (log_date) TABLESPACE trackingindexspace;" );

Error about a lock:

2009-10-07 22:18:02 PDT clsdb postgres 10.13.200.70(46706) ERROR:  deadlock detected

2009-10-07 22:18:02 PDT clsdb 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 clsdb 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

Thanks

Tory