Subject: Re: Unique constraint across all partitions?
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <8ae682ca-2761-7cd5-d844-7e40bf5a7989@gmail.com>
Date: Tue, 26 Jan 2021 08:21:09 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at>
Content-Type: multipart/alternative;
 boundary="------------0A675DDC96E4C233756C7196"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------0A675DDC96E4C233756C7196
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.

--------------0A675DDC96E4C233756C7196
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 1/26/21 2:33 AM, Laurenz Albe wrote:<br>
    <blockquote type="cite"
      cite="mid:5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at">
      <pre class="moz-quote-pre" wrap="">On Mon, 2021-01-25 at 18:09 -0600, Ron wrote:
</pre>
      <blockquote type="cite">
        <blockquote type="cite">
          <blockquote type="cite">
            <blockquote type="cite">
              <pre class="moz-quote-pre" wrap="">Is it possible to enforce a unique constraint across all partitions for 
either declarative or inheritance based partitioning? Preferably 
declarative based.
</pre>
            </blockquote>
          </blockquote>
          <pre class="moz-quote-pre" wrap="">
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.
</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap=""> 
This is why global indices are needed.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
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.</pre>
    </blockquote>
    <br>
    Don't make my decisions for me.<br>
    <br>
    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 <b>25 years</b>. 
    What's just as useful are indices partitioned on keys which have
    nothing to do with the table's partition key.)<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------0A675DDC96E4C233756C7196--