Re: Unique constraint across all partitions?

Поиск
Список
Период
Сортировка
От Ron
Тема Re: Unique constraint across all partitions?
Дата
Msg-id 8ae682ca-2761-7cd5-d844-7e40bf5a7989@gmail.com
обсуждение исходный текст
Ответ на Re: Unique constraint across all partitions?  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-admin
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.

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Karsten Lenz (KLE)"
Дата:
Сообщение: RE: productive usage of PostgreSQL with Windows Server
Следующее
От: Binghan
Дата:
Сообщение: How to change mode for cluster log?