Date: Mon, 6 Aug 2018 10:02:01 +0200
From: "Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: Keith <keith@keithf4.com>, pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL 11 global index
Message-ID: <20180806100201.42a3c794@firost>
In-Reply-To: 
 <CA+t6e1=eJZotPr_cDh-b8PoYq3XXT3B1zh1-kyr_6AoH11GEkA@mail.gmail.com>
References: 
 <CA+t6e1=uvR0PP_K8FtvMuebzrAofyzsTbMJoQB1pzimTBwfmOA@mail.gmail.com>
	<CAHw75vtyx-VHPY6tWQSYf52ofDG4D8pCZ6N3E_ZwS5yUmb88nQ@mail.gmail.com>
	<CA+t6e1=eJZotPr_cDh-b8PoYq3XXT3B1zh1-kyr_6AoH11GEkA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi,

On Mon, 6 Aug 2018 09:20:45 +0300
Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

> The solution you suggested arent helpfull (both unique index and
> pg_partman) because I need to make sure that in all the partitions I have a
> specific column that is unique.

Here is a workaround that actually implement a unique constraint over multi
relation. You can avoid first chapters about the problems a UNIQUE constraint
deal with. The following link jump directly to the solution:
http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html#real-solution-adding-locks

Regards,

> 2018-08-05 23:31 GMT+03:00 Keith <keith@keithf4.com>:
> 
> >
> >
> > On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky <  
> > mariel.cherkassky@gmail.com> wrote:  
> >  
> >> Hi,
> >> I read the documentation but i didnt find any word regarding global
> >> index. I saw a new feature that indexes that  exist on the parent
> >> automaticly created on the childs but is there any connection between the
> >> indexes ?
> >>
> >> I'm trying to make sure that 2 different partitions wont have the same
> >> data on some of the columns and the partition col isnt one of those column.
> >> In oracle that kind of index is called global index.
> >>
> >> Do you now some third extension maybe that allow you to use such feature
> >> ?
> >>
> >> Thanks , Mariel.
> >>  
> >
> > This feature is not yet supported in PostgreSQL. In PG11, you can create a
> > unique index, but in order for it to apply to the entire partition set, the
> > column must be part of the partition key. I don't believe the native
> > partitioning feature even allows you to create an unique index on the
> > parent table if the partition key isn't part of it.
> >
> > I've found some work-arounds for this in pg_partman in the mean time.
> >
> > https://github.com/pgpartman/pg_partman
> >
> > To support non-partition key unique columns on native partition sets, I
> > have it use a separate template table where you apply your indexes instead
> > of the parent table. And while it will enforce the uniqueness per child
> > table, it will not enforce it across the entire set. To at least watch for
> > this happening, I've provided a python script that goes through all the
> > child tables and checks for any duplicates across the whole set. So it
> > won't catch it at the time of insertion, but it should at least let you
> > know if/when it happens.
> >
> > Keith
> >  


-- 
Jehan-Guillaume de Rorthais
Dalibo