Hi, I've just joined the list, as discussion on the [GENERAL] pointed me to a thread here, which I'm very interested in.
But I'm not quite at "hacker" level, so pls forgive me if what I'll say is naive for you here. To the point. ... with a little introduction first: On [GENERAL] I've been advised to change my schema from inheritance hierarchy to a flat table .... as I've explained, that I need an ID that'll be unique across the entire hierarchy. But this is impossible for me, because ..... <ELABORATED_EXAMPLE_SKIP_ON_FIRST_READ> .....the data I have there requires me to have two different indices (as FK targets from elsewhere) over dissjoined parts of the dataset: CREATE TABLE tt (ID int, A int, B int, c bool) -- rows are like (1,1,1,true) CREATE TABLE t1 (primary key (id,a), check (c=true)) INHERITS (tt); -- here (1,1,1,true) must raise error when (1,1,2,true) is present CREATE TABLE t2 (primary key (id,b), check (c=false)) INHERITS (tt); -- here (1,1,1,false) must raise error when (1,2,1,false) is present -- but if this two rows were 'true' instead of 'false', they are -- entirely acceptable. With this, I understand that I fall into the usage scenario you've identified as "FK into the master table global index". Consequently, you are planning to put "table object selector" into the "global index", so that FK pointing to master table index could get redirected into relevant subtable containing actual data. I may be mistaken as I haven't followed (greed the archived) all your discussion on this subject, but I haven't seen you discussing another implementation - a functional index; something that want be as universal as your plan, but (IMHO) can be much easier to implement. Assuming using TT/T1/T2 tables example from above (based on my actual data): 1. I use "FUNCTION myindex(id,a,b,c)" to create unique indexes on all subtables. 2. It would be very hard for me to work out similar function that returns !!Correctly unique!! values for every row in every subtable - from the example above you can see, that "unique" by just concatenating the fields is not correct in theis use case. 3. but (as per -1- above) it's doable and easy to make such function for every subtable. It just concatenates som, and ignores other columns, based on the "selector column". So I would like to propose a mechanism for "global index", using functional indes as: A) have a FUNCTION, with argument list covering columns, that are used in CHECK constraint partitioning the table. B) allow for that function to be used throughout the entire hierarchy of inheritance. C) using values return by that function, create actual indeces only on the LEAF subtables. D) "register" that function - it's argument list in particular - at every lever of inheritance hierarchy. E) at every level of the inheritance hierarchy, to select appropriate subcolumn, use only the CHECK constraints against relevant column being an argument to the function being registered as functional-index there. This way we get FK subtable selection is *identical* to that selection on INSERT/UPDATE/DLETE/SELECT actions; we don't have any additional disk-space consummed (needing to vacuum) by the "global index"; we don't have to keep the "table object" withing the hierarchy indeces; and in consequence, the drop of a subtable from the hierarchy is as chip as today. </ELABORATED_EXAMPLE_SKIP_ON_FIRST_READ> In short, this proposal is: 1. creation of functional index on parent table of inheritance tree does not actually create any (material) index 2. instead, it does install "processing hooks" and "FK virtual targets", so that: 2.1 it is possible to create FK targeting it. 2.2 processing such dereference (resolution of the actual: subtable, row) is based on CHECK constraints of subtables present. -R -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers