reindex and copy - deadlock?

From: Litao Wu <litaowu(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: pgsql-performance(at)postgresql(dot)org
Subject: reindex and copy - deadlock?
Date: 2004-06-08 15:31:06
Message-ID: 20040608153106.61670.qmail@web13123.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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/

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2004-06-08 16:47:08 Re: reindex and copy - deadlock?
Previous Message Scott Marlowe 2004-06-08 15:27:16 Re: Join slow on "large" tables