Feedback-ID: i76984098:Fastmail
Message-ID: <94e1205e-8ddf-403b-8477-02d7939f2905@aklaver.com>
Date: Thu, 13 Feb 2025 09:00:07 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Best Approach for Swapping a Table with its Copy
To: Dominique Devienne <ddevienne@gmail.com>,
 Greg Sabino Mullane <htamfids@gmail.com>
Cc: Marcelo Fernandes <marcefern7@gmail.com>,
 pgsql-general@lists.postgresql.org
References: <CAM2F1VP9nn0F6fSjbmhJCNSjv-dbAJcAytZ2HkTkZ0dGYZvxrw@mail.gmail.com>
 <8ac6a1dd-db82-4ea7-8186-0c354d79049f@aklaver.com>
 <CAM2F1VOOn1izCrtcrDx4YUtCY-H64Vj5yvN5H1Lhk8kCY+_W6Q@mail.gmail.com>
 <9fa75099-1ee9-43de-ac6e-945841b184eb@aklaver.com>
 <CAM2F1VONo8M9gcybhmf8-CdmRUC2QrFGZkZLYF-+uLZuhRKaxQ@mail.gmail.com>
 <22406cdf-83f5-4b98-b07e-7827afe97f93@aklaver.com>
 <CAM2F1VMdDSxLhccn=0pXriJrR6a7Oe9Kir1DB_0pW1v88bsvCg@mail.gmail.com>
 <CAKAnmmJ04kH_gWR+cOFWD0h63RDck1k6hqQYH8K30-cfCvtO_Q@mail.gmail.com>
 <CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAFCRh-9xC0CXKM2GTQBfjYY5GQx29Uit+fY5PpBFTvX927YC8w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/94e1205e-8ddf-403b-8477-02d7939f2905%40aklaver.com>
Precedence: bulk

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