public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Dominique Devienne <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: Marcelo Fernandes <[email protected]>
Cc: [email protected]
Subject: Re: Best Approach for Swapping a Table with its Copy
Date: Thu, 13 Feb 2025 09:00:07 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
References: <CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com>
<[email protected]>
<CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
<[email protected]>
<CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
<[email protected]>
<CAM2F1VMdDSxLhccn=0pXriJrR6a7Oe9Kir1DB_0pW1v88bsvCg@mail.gmail.com>
<CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
<CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
On 2/13/25 07:25, Dominique Devienne wrote:
> On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane <[email protected]
> <mailto:[email protected]>> 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.g...
"
> 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
[email protected]
view thread (13+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Best Approach for Swapping a Table with its Copy
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox