Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 4943565B21F for ; Tue, 16 Mar 2010 14:00:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 75559-02-3 for ; Tue, 16 Mar 2010 17:00:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 0603A797880 for ; Tue, 16 Mar 2010 13:16:06 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o2GGG4Tf010100; Tue, 16 Mar 2010 12:16:04 -0400 (EDT) To: Thomas Kenner cc: pgsql-sql@postgresql.org Subject: Re: Rename Index - Deadlock In-reply-to: <201003161045.58639.thomas.kenner@gmail.com> References: <201003161045.58639.thomas.kenner@gmail.com> Comments: In-reply-to Thomas Kenner message dated "Tue, 16 Mar 2010 10:45:58 +0100" Date: Tue, 16 Mar 2010 12:16:04 -0400 Message-ID: <10099.1268756164@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.459 tagged_above=-10 required=5 tests=AWL=0.140, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/106 X-Sequence-Number: 33813 Thomas Kenner writes: > Each day I'm recreating the index my_index of the table my_table. Therefore I > create a new index my_index_new, drop the old index my_index, and rename the > new index: > ALTER INDEX my_index_new RENAME TO my_index; > If an insert or select statement is run at the same time as the "ALTER INDEX > ... RENAME TO" statement, it will end up in a deadlock. I doubt it's the RENAME that's the problem, more likely the DROP of the old index. That has to get an exclusive lock on the parent table to be sure there is nothing trying to use the index. Why exactly do you feel you need to do this every day? regards, tom lane