On Wed, Dec 20, 2017 at 12:01 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > I have two patches to rebase on top of this, which I hope to post later > today: > > 1) let these indexes be unique (i.e. allow unique and PK constraints) > 2) allow foreign keys on partitioned tables > > I have a further patch to allow FOR EACH ROW triggers on partitioned > tables, but I think it's largely unrelated to this (and, as I was > surprised to discover, it's also unrelated to the FKs one).
Sounds great! I made the comment during my talk at PGCONF.EU that partitioned tables in PostgreSQL are really just a bunch of tables glued together, but that over time "we'll make the glue better", and I think the improvements on which you are working will go a long way in that direction. With regard to indexes, presumably we can only have a unique index if the index definition matches the partition key definition. Otherwise, we can only guarantee per-partition uniqueness, unless (1) we implement "global" indexes, which sounds like a patch that would take a fair amount of work, or (2) every index insertion goes and checks for conflicting values in all of the "sibling" indexes. This latter approach was suggested to me by Andres, and certainly solves a bunch of problems with vacuuming and dropping indexes, but seems like it would probably have lousy insert performance unless the number of partitions is very small. With regard to foreign keys, it seems like there are two directions to worry about. An "outbound" foreign key needs to cascade, I think, more or less like an index does, with every child inheriting the foreign key from the partition root. An "inbound" foreign key just needs a unique index to point at and depend on. Given the work you are doing on unique indexes, I'm guessing that you are talking about supporting the latter, not the former, but the way you phrased it sounds like the opposite. I haven't thought much about the possibility of supporting FOR EACH ROW triggers, but it sounds like a good idea if we can do it. I'm not sure how that will work, though, if users directly access the leaf partitions. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
