Date: Mon, 6 Aug 2018 12:30:50 +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: <20180806123050.1fab3011@firost>
In-Reply-To: 
 <CA+t6e1=djbUK3XN8PshRETvO_9D92HWgqdf7iw=arRHZ7knbEw@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>
	<20180806100201.42a3c794@firost>
	<CA+t6e1=djbUK3XN8PshRETvO_9D92HWgqdf7iw=arRHZ7knbEw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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 ne=
ed
> 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:

  =C2=AB COPY FROM will invoke any triggers and check constraints on the de=
stination
  table. However, it will not invoke rules.=C2=BB

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>: =20
>=20
> > Hi,
> >
> > On Mon, 6 Aug 2018 09:20:45 +0300
> > Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
> > =20
> > > The solution you suggested arent helpfull (both unique index and
> > > pg_partman) because I need to make sure that in all the partitions I =
=20
> > have a =20
> > > specific column that is unique. =20
> >
> > Here is a workaround that actually implement a unique constraint over m=
ulti
> > 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,
> > =20
> > > 2018-08-05 23:31 GMT+03:00 Keith <keith@keithf4.com>:
> > > =20
> > > >
> > > >
> > > > On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky < =20
> > > > mariel.cherkassky@gmail.com> wrote: =20
> > > > =20
> > > >> 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 betw=
een =20
> > the =20
> > > >> 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 thos=
e =20
> > column. =20
> > > >> In oracle that kind of index is called global index.
> > > >>
> > > >> Do you now some third extension maybe that allow you to use such =
=20
> > feature =20
> > > >> ?
> > > >>
> > > >> Thanks , Mariel.
> > > >> =20
> > > >
> > > > This feature is not yet supported in PostgreSQL. In PG11, you can =
=20
> > create a =20
> > > > unique index, but in order for it to apply to the entire partition =
=20
> > set, the =20
> > > > 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 t=
he
> > > > 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 tim=
e.
> > > >
> > > > https://github.com/pgpartman/pg_partman
> > > >
> > > > To support non-partition key unique columns on native partition set=
s, I
> > > > have it use a separate template table where you apply your indexes =
=20
> > instead =20
> > > > of the parent table. And while it will enforce the uniqueness per c=
hild
> > > > table, it will not enforce it across the entire set. To at least wa=
tch =20
> > for =20
> > > > 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
> > > > =20
> >
> >
> >
> > --
> > Jehan-Guillaume de Rorthais
> > Dalibo
> > =20


--=20
Jehan-Guillaume de Rorthais
Dalibo