On 2/13/25 07:25, Dominique Devienne wrote:
> On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane <htamfids@gmail.com
> <mailto:htamfids@gmail.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.klaver@aklaver.com
