Content-Type: multipart/alternative;
 boundary="------------nd7iUZiQWxu5pVXVpcdsa84v"
Message-ID: <f5b632d2-4549-9869-b971-5199fe7b5830@gmail.com>
Date: Sun, 27 Nov 2022 16:07:22 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.2.2
Subject: Re: Disable unique constraint in Postgres
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CALXkTpyQXcT5m7cYttWjLv3n8xGC+MFYVah-q9Lxz-8FKBoMmg@mail.gmail.com>
 <CAAo1mbmvoxTcPdBXdAtZ4oPT-kAOouUpKeYDbqbUsJdFc0CsbA@mail.gmail.com>
 <B7F0896B-C1E1-4858-9CC6-A9FAD31727CC@elevated-dev.com>
 <ed261f0d-2d2c-3d40-7963-8d44defd8a40@gmail.com>
 <88df5cb3-913f-182e-22ed-cbdc62fdd7d6@gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: <88df5cb3-913f-182e-22ed-cbdc62fdd7d6@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/f5b632d2-4549-9869-b971-5199fe7b5830%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------nd7iUZiQWxu5pVXVpcdsa84v
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 11/27/22 15:56, Mladen Gogala wrote:
> On 11/27/22 16:23, Ron wrote:
>>
>> Being an "enterprise dba", IMNSHO "disable constraint" (and more 
>> specifically "*disable index*") is a /great/ feature for maintaining 
>> tables.  For example, if you need to purge a *lot* of records into a 
>> table, disable all indices except the index supporting your WHERE CLAUSE, 
>> delete the data, and then re-enable the indices.  Bonus points if the 
>> REENABLE commands can be done in parallel.
>>
>> Sure, you /can/ dig around for all of the CREATE INDEX statements, but 
>> that leads to possible errors: "oops, forgot to recreate one of them", or 
>> "typo caused one to fail".  OTOH, DISABLE INDEX and REENABLE INDEX are 
>> idiot-proof, and can be automated.
>>
>> -- 
>> Angular momentum makes the world go 'round.
>
>
> You are aware that in Oracle "ALTER TABLE ENABLE CONSTRAINT" rebuilds the 
> index used to enforce the constraint? You will not save any time by 
> disabling and re-enabling constraints.
>

Eliminating mistakes and reducing DBA work are the point of DISABLE and 
ENABLE INDEX, not saving clock time during the maintenance window.

-- 
Angular momentum makes the world go 'round.
--------------nd7iUZiQWxu5pVXVpcdsa84v
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 11/27/22 15:56, Mladen Gogala wrote:<br>
    <blockquote type="cite"
      cite="mid:88df5cb3-913f-182e-22ed-cbdc62fdd7d6@gmail.com">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <div class="moz-cite-prefix">On 11/27/22 16:23, Ron wrote:<br>
      </div>
      <blockquote type="cite"
        cite="mid:ed261f0d-2d2c-3d40-7963-8d44defd8a40@gmail.com"> <br>
        Being an "enterprise dba", IMNSHO "disable constraint" (and more
        specifically "<b>disable index</b>") is a <i>great</i> feature
        for maintaining tables.  For example, if you need to purge a <b>lot</b>
        of records into a table, disable all indices except the index
        supporting your WHERE CLAUSE, delete the data, and then
        re-enable the indices.  Bonus points if the REENABLE commands
        can be done in parallel.<br>
        <br>
        Sure, you <i>can</i> dig around for all of the CREATE INDEX
        statements, but that leads to possible errors: "oops, forgot to
        recreate one of them", or "typo caused one to fail".  OTOH,
        DISABLE INDEX and REENABLE INDEX are idiot-proof, and can be
        automated.<br>
        <br>
        <div class="moz-signature">-- <br>
          Angular momentum makes the world go 'round.</div>
      </blockquote>
      <p><br>
      </p>
      <p>You are aware that in Oracle "ALTER TABLE ENABLE CONSTRAINT"
        rebuilds the index used to enforce the constraint? You will not
        save any time by disabling and re-enabling constraints. <br>
      </p>
    </blockquote>
    <br>
    Eliminating mistakes and reducing DBA work are the point of DISABLE
    and ENABLE INDEX, not saving clock time during the maintenance
    window.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------nd7iUZiQWxu5pVXVpcdsa84v--