Re: PostgreSQL 11 global index

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