Re: PostgreSQL 11 global index

hI Jehan-Guillaume de Rorthais,
This solution looks good but I think that i will have a big problem. I need
a global index because I try to insert a bulk data (alot of data) with the
copy command / pgbulkload extension. The solution you provided will create
a constraint trigger that will be used after very insert. Now, in my case I
have 2 questions :
1)When I use the copy command to load data into the table, do you think
that the trigger will be effected ?
2)If the trigger will work I think that it would decrease the performance
dramaticly.

2018-08-06 11:02 GMT+03:00 Jehan-Guillaume (ioguix) de Rorthais <
ioguix(at)free(dot)fr>:

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