Обсуждение: PostgreSQL 11 global index
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.
Postgres 11 supports CREATE UNIQUE INDEX on partitionned tables, maybe that could be the answer for your question ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
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.
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
Hi,
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. In otherwords one column can have the same value in two different partitions and that is the concept of the global index.
Thanks , Mariel.
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.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
Mariel Cherkassky 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. > In otherwords one column can have the same value in two different partitions and that > is the concept of the global index. There is no way to guarantee uniqueness across partitions in PostgreSQL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.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-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
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@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
+1 Very interesting article Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
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
Hello, I was wondering if a simple trigger inserting ID values in a dedicated table with an UNIQUE key would have a chance to give similar performances than you trigger checking ID with SELECT count(1) > 1 FROM tabpart WHERE id = NEW.id knowing that both triggers gives the same concurrency benefits ... Find attached a little test case on my PostgreSQL 11beta2 on x86_64-w64-mingw32, compiled by gcc.exe (x86_64-win32-seh-rev1, Built by MinGW-W64 project) 7.2.0, 64-bit inserting 100.000 lignes in a non partitionned table with a unique KEY takes 627 ms inserting 100.000 lignes in a partitionned table (10 part) with "SELECT COUNT" trigger takes 6696 ms inserting 100.000 lignes in a partitionned table (10 part) with "INSERT ID values" trigger takes 1634 ms Is that ratio the same on a Linux box ? I'm suspecting parsing time for "SELECT count(1) > 1 FROM tabpart WHERE id = NEW.id", is that possible ? test_gbl_index.sql <http://www.postgresql-archive.org/file/t348768/test_gbl_index.sql> Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
Parse time & execution time. Why are you not just using a sequence? Do you have an application which requires contiguous numbers? -- Scott Ribe scott_ribe@elevated-dev.com https://www.linkedin.com/in/scottribe/ > On Aug 13, 2018, at 4:39 PM, legrand legrand <legrand_legrand@hotmail.com> wrote: > > I'm suspecting parsing time for "SELECT count(1) > 1 FROM tabpart WHERE id = > NEW.id",
> > On Aug 13, 2018, at 4:44 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote: > > Parse time & execution time. > I expect using an ordinary sequence and setting it as the column's default would be fastest.