[HACKERS] global indices

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