pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Disable unique constraint in Postgres
Date: Sun, 27 Nov 2022 16:07:22 -0600
Message-ID: <f5b632d2-4549-9869-b971-5199fe7b5830@gmail.com> (raw)
In-Reply-To: <88df5cb3-913f-182e-22ed-cbdc62fdd7d6@gmail.com>
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>

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.

view thread (9+ messages)  latest in thread

Message-ID: <f5b632d2-4549-9869-b971-5199fe7b5830@gmail.com>
Permalink:  ../f5b632d2-4549-9869-b971-5199fe7b5830@gmail.com/
Also on:    postgresql.org/message-id/f5b632d2-4549-9869-b971-5199fe7b5830@gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
  Subject: Re: Disable unique constraint in Postgres
  In-Reply-To: <f5b632d2-4549-9869-b971-5199fe7b5830@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox