Обсуждение: Unique constraint across all partitions?

All;


Is it possible to enforce a unique constraint across all partitions for 
either declarative or inheritance based partitioning? Preferably 
declarative based.


Thanks

On 1/25/21 4:44 PM, S Bob wrote:
> All;
>
>
> Is it possible to enforce a unique constraint across all partitions for 
> either declarative or inheritance based partitioning? Preferably 
> declarative based.

You can create unique indexes on parent tables.  Or am I misunderstanding you?

-- 
Angular momentum makes the world go 'round.

On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr@gmail.com> wrote:

On 1/25/21 4:44 PM, S Bob wrote:

> Is it possible to enforce a unique constraint across all partitions for
> either declarative or inheritance based partitioning? Preferably
> declarative based.

You can create unique indexes on parent tables.  Or am I misunderstanding you?

You seem to misunderstand how partitions and unique constraints work.

Each partition gets its own copy of the unique index/constraint and ensures that duplicates do not appear therein.  If the specification for the unique constraint is something like "id bigint" then the value 5 can appear on multiple partitions just fine - the partition key(s) effectively become part of the unique constraint/index even though the values are not actually stored in the index.

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.

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
uniquenessacross 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.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe schrieb am 26.01.2021 um 09:33:
>>> 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.

While not extremely popular, many Oracle users do find them useful.

> 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.

I wonder why they would make the "best parts" slow?

I would expect that partition pruning or partition wise joins are unaffected
by that (which I do consider one of the "best parts")

Obviously the overhead of maintaining the index is there, especially when
dropping partitions this would mean a substantial overhead.

But it's up to the user to decide if the benefits (proper foreign keys, uniqueness)
outweigh the disadvantages.

Is there any other downside I am missing?

Would DML statements be affected (if you factor out the cost of maintaining
the global index)?

Thomas

Kanninen Anssi EXT schrieb am 26.01.2021 um 11:09:
> But why use global indexes?

To have unique constraints for values that should be unique across all partitions.

That also allows foreign keys _referencing_ the partitioned table.

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.