Re: Best Approach for Swapping a Table with its Copy - Mailing list pgsql-general

> On 12 Feb 2025, at 22:27, Marcelo Fernandes <marcefern7@gmail.com> wrote:
>
> On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver
>
> Also pulling in your question in the other reply:
>
>> Why can't you just add the exclusion constraint to the original table?
>
> With unique constraints, one can use a unique index to create the constraint
> concurrently.
>
> With check constraints, one can create the constraint as invalid and then
> validate it while only requiring a share update exclusive lock.
>
> 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.
>
> This is done to avoid having to hold an exclusive lock for a long amount of
> time, thus creating application outages.
>

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?

—
Michal