From: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: Keith <keith@keithf4.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL 11 global index
Date: Mon, 6 Aug 2018 10:02:01 +0200
Message-ID: <20180806100201.42a3c794@firost> (raw)
In-Reply-To: <CA+t6e1=eJZotPr_cDh-b8PoYq3XXT3B1zh1-kyr_6AoH11GEkA@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>
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...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ioguix@free.fr, mariel.cherkassky@gmail.com, keith@keithf4.com, pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL 11 global index
In-Reply-To: <20180806100201.42a3c794@firost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox