From: Ron <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Unique constraint across all partitions?
Date: Tue, 26 Jan 2021 08:21:09 -0600
Message-ID: <8ae682ca-2761-7cd5-d844-7e40bf5a7989@gmail.com> (raw)
In-Reply-To: <5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at>
References: <e46db75f-02fc-1a79-5d04-92bc5ea05216@quadratum-braccas.com>
<3d00c1f4-646c-59b7-e4dc-1d51190d2139@gmail.com>
<CAKFQuwZx-3A4Oqk69DTBk0MaVa9W7f=LPnmQpx1Q5Nj+=9PPVw@mail.gmail.com>
<e91069b5-f6a8-c812-a4c9-9c5c4383cb4f@gmail.com>
<5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at>
On 1/26/21 2:33 AM, Laurenz Albe wrote:
> On Mon, 2021-01-25 at 18:09 -0600, Ron wrote:
>>>>> Is it possible to enforce a unique constraint across all partitions for
>>>>> either declarative or inheritance based partitioning? Preferably
>>>>> declarative based.
>>> So, yes, so long as your unique constraint takes into consideration the partitioning scheme it will enforce uniqueness across all of the partitions. But the answer to the full, but possibly
>>> implied, question is no.
>>
>> This is why global indices are needed.
> Global indexes are likely more pain than gain.
>
> They will make the best part of partitioning (attaching and detaching
> partitions) painfully slow. Hardly worth it to gain a tiny little bit
> of integrity. Within a partition, you can guarantee uniqueness easily.
Don't make my decisions for me.
When a PK or FK without an embedded partition key are needed, you bite the
bullet and take the 9 hour downtime. (A legacy RDBMS I occasionally still
maintain has had them for *25 years*. What's just as useful are indices
partitioned on keys which have nothing to do with the table's partition key.)
--
Angular momentum makes the world go 'round.
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: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Unique constraint across all partitions?
In-Reply-To: <8ae682ca-2761-7cd5-d844-7e40bf5a7989@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox