Re: PostgreSQL 11 global index - Mailing list pgsql-admin

On Mon, 6 Aug 2018 11:48:04 +0300
Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

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

NB: it could be fired "before insert" as well depending on your schema, with
minimal modification (see comments in the article)

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

The doc says yes:

  « COPY FROM will invoke any triggers and check constraints on the destination
  table. However, it will not invoke rules.»

https://www.postgresql.org/docs/11/static/sql-copy.html

> 2)If the trigger will work I think that it would decrease the performance
> dramaticly.

You will have to measure it.

I doubt rewriting the trigger in C would help on this matter, but if you do
test it, I would be glad to hear about the results :)

> 2018-08-06 11:02 GMT+03:00 Jehan-Guillaume (ioguix) de Rorthais <
> ioguix@free.fr>:
>
> > 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
> >



--
Jehan-Guillaume de Rorthais
Dalibo