Re: Best Approach for Swapping a Table with its Copy

On 2/13/25 07:25, Dominique Devienne wrote:
> On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com
> <mailto:htamfids(at)gmail(dot)com>> wrote:
>
>
> Thanks for the colorful analogy Greg :).
>
> Maybe the better option is to support ALTER TABLE to ADD an exclusion
> constraint, no?

That exists:

select version();
PostgreSQL 14.15

create table exclusion_test(id integer primary key, dt1 timestamptz, dt2
timestamptz);

ALTER TABLE exclusion_test ADD CONSTRAINT dt_overlap
EXCLUDE USING gist (
id WITH =,
tstzrange(dt1, dt2, '[]') WITH &&
);

\d exclusion_test
Table "public.exclusion_test"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
id | integer | | not null |
dt1 | timestamp with time zone | | |
dt2 | timestamp with time zone | | |
Indexes:
"exclusion_test_pkey" PRIMARY KEY, btree (id)
"dt_overlap" EXCLUDE USING gist (id WITH =, tstzrange(dt1, dt2,
'[]'::text) WITH &&)

> I get that it's not support now. But is it more difficult than the
> above? And why then? --DD

From here:

https://www.postgresql.org/message-id/CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY%2B_W6Q%40mail.gmail.com

"
> 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.

Hope that clarifies the situation a bit better
"

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com