On Wed, Mar 07, 2007 at 02:43:39AM +1030, Shane Ambler wrote: > I think a way can be devised to maintain the primary key and unique > constraints. > If a search is done on the parent table, the planner knows to rewrite > the query as a union (or union all) of all child tables that relate to > the where clause, or all child tables if the where clause is not on the > column/s used to partition, then this concept should be able to be > converted to indexes as well, so that when a primary or unique index > from a child table is inserted to, then each of the related child > indexes is consulted to ensure uniqueness.
But that's where it breaks down: you not only need to check that the row you're inserting is unique, you need to make sure that other people trying to insert the same value see it. After all, a unique index does contain multiple instances of any particular value, it's just that the DB has verified that none of these are visible to the same transaction. If there is a collision a lock is taken that waits until the colliding insert commits or rejects. With b-tree it's easy, every value can only appear in one place, so it's a very simple lock. The reason why GiST doesn't support unique indexes is due to the fact that any particular value could appear in many places, so to stop concurrent inserts you need to lock *every* page in the tree that another backend might want to insert the record. I fear this will also be a problem for indexes over multiple tables, you will need to lock the page in every index that some other DB might want to insert the value into. All while avoiding deadlocks. I think the problems are related. If you can solve it for multiple tables, you can solve it for GiST indexes also. > This would only apply if the partitioning was not done by the primary or > unique column. That's the easy case, no problem there. Have a nice day, -- Martijn van Oosterhout <firstname.lastname@example.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate.
Description: Digital signature