Re: PostgreSQL 11 global index

Hi,

On Mon, 6 Aug 2018 09:20:45 +0300
Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)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(at)keithf4(dot)com>:
>
> >
> >
> > On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky <
> > mariel(dot)cherkassky(at)gmail(dot)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