X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BC84CD1B278 for ; Tue, 8 Jun 2004 12:31:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19774-09 for ; Tue, 8 Jun 2004 15:31:09 +0000 (GMT) Received: from web13123.mail.yahoo.com (web13123.mail.yahoo.com [216.136.174.141]) by svr1.postgresql.org (Postfix) with SMTP id C8F62D1B241 for ; Tue, 8 Jun 2004 12:31:07 -0300 (ADT) Message-ID: <20040608153106.61670.qmail@web13123.mail.yahoo.com> Received: from [63.78.248.48] by web13123.mail.yahoo.com via HTTP; Tue, 08 Jun 2004 08:31:06 PDT Date: Tue, 8 Jun 2004 08:31:06 -0700 (PDT) From: Litao Wu Subject: reindex and copy - deadlock? To: pgsql-performance@postgresql.org Cc: pgsql-performance@postgresql.org In-Reply-To: <40C5CED2.3020002@lamundial.hn> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/91 X-Sequence-Number: 7149 Hi, We often experience with the problem that reindex cannot be finished in our production database. It's typically done with 30 minutes. However, sometimes, when there is another "COPY" process, reindex will not finish. By monitoring the CPU time reindex takes, it does not increase at all. That seems a deadlock. But the following query shows only reindex process (23127)is granted lock while COPY process (3149) is not. Last time when we have this problem and kill reindex process and COPY process does not work. We had to bounce the database server. As you know, when reindex is running, nobody can access the table. Can someone kindly help? Thanks, Here is lock info from database: replace | database | transaction | pid | mode | granted -----------------------+----------+-------------+-------+---------------------+--------- email | 17613 | | 3149 | RowExclusiveLock | f email_cre_dom_idx | 17613 | | 23127 | ExclusiveLock | t email_cid_cre_idx | 17613 | | 23127 | ShareLock | t email_cid_cre_idx | 17613 | | 23127 | AccessExclusiveLock | t email | 17613 | | 23127 | ShareLock | t email | 17613 | | 23127 | AccessExclusiveLock | t email_cid_cre_dom_idx | 17613 | | 23127 | ShareLock | t email_cid_cre_dom_idx | 17613 | | 23127 | AccessExclusiveLock | t email_did_cre_idx | 17613 | | 23127 | ShareLock | t email_did_cre_idx | 17613 | | 23127 | AccessExclusiveLock | t email_cre_dom_idx | 17613 | | 23127 | AccessExclusiveLock | t (11 rows) Here are the processes of 3149 and 23127 from OS: postgres 3149 1.3 6.4 154104 134444 ? S Jun03 92:04 postgres: postgres db1 xx.xx.xx.xx COPY waiting postgres 23127 3.2 9.3 228224 194512 ? S 03:35 15:03 postgres: postgres db1 [local] REINDEX Here are queries from database: 23127 | REINDEX table email 3149 | COPY email (...) FROM stdin __________________________________ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/