Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.400.131.1.6\))
Subject: Re: Best Approach for Swapping a Table with its Copy
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
In-Reply-To: <CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
Date: Fri, 14 Feb 2025 07:01:55 +0100
Cc: Adrian Klaver <adrian.klaver@aklaver.com>,
 pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <44BB6A74-0AB1-462C-B13A-2CF106DA70D6@kleczek.org>
References: <CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com>
 <8ac6a1dd-db82-4ea7-8186-0c354d79049f@aklaver.com>
 <CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
To: Marcelo Fernandes <marcefern7@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/44BB6A74-0AB1-462C-B13A-2CF106DA70D6%40kleczek.org>
Precedence: bulk


> On 12 Feb 2025, at 22:27, Marcelo Fernandes <marcefern7@gmail.com> =
wrote:
>=20
> On Thu, Feb 13, 2025 at 10:02=E2=80=AFAM Adrian Klaver
>=20
> Also pulling in your question in the other reply:
>=20
>> Why can't you just add the exclusion constraint to the original =
table?
>=20
> With unique constraints, one can use a unique index to create the =
constraint
> concurrently.
>=20
> With check constraints, one can create the constraint as invalid and =
then
> validate it while only requiring a share update exclusive lock.
>=20
> But with exclusion constraints, neither of those techniques are =
available. In
> that sense, there is no way to create this type of constraint in a =
large table
> without copying the original table, adding the constraint, and =
performing a
> table swap.
>=20
> This is done to avoid having to hold an exclusive lock for a long =
amount of
> time, thus creating application outages.
>=20

Just a wild idea (not sure if anyone suggested it in this thread and not =
sure if it is doable):

Create index concurrently and then fiddle with the catalog tables to =
define the constraint using this index?

=E2=80=94
Michal