From: Mladen Gogala <gogala.mladen@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Disable unique constraint in Postgres
Date: Sun, 27 Nov 2022 16:56:03 -0500
Message-ID: <88df5cb3-913f-182e-22ed-cbdc62fdd7d6@gmail.com> (raw)
In-Reply-To: <ed261f0d-2d2c-3d40-7963-8d44defd8a40@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>
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
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: gogala.mladen@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Disable unique constraint in Postgres
In-Reply-To: <88df5cb3-913f-182e-22ed-cbdc62fdd7d6@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