Content-Type: multipart/alternative;
 boundary="------------5cLMSAWtD9OmDcSoMjFac0Ic"
Message-ID: <88df5cb3-913f-182e-22ed-cbdc62fdd7d6@gmail.com>
Date: Sun, 27 Nov 2022 16:56:03 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.5.0
Subject: Re: Disable unique constraint in Postgres
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>
Content-Language: en-US
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: <ed261f0d-2d2c-3d40-7963-8d44defd8a40@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/88df5cb3-913f-182e-22ed-cbdc62fdd7d6%40gmail.com>
Precedence: bulk

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

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.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------5cLMSAWtD9OmDcSoMjFac0Ic
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>
    <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>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------5cLMSAWtD9OmDcSoMjFac0Ic--