Re: [HACKERS] Adding PRIMARY KEY info

D'Arcy J.M. Cain wrote:
>
> >
> > pg_index is good place for all _3_ key types because of:
> >
> > 1. index should be created for each foreign key -
> >    just for performance.
> > 2. pg_index already has int28 field for key attributes.
> > 3. pg_index already has indisunique (note that foreign keys
> >    may reference unique keys, not just primary ones).
> >
> > - so we have just add two fields to pg_index:
> >
> > bool indisprimary;
> > oid  indreferenced;
> > ^^^^^^^^^^^^^^^^^^
> > this is for foreign keys: oid of referenced relation'
> > primary/unique key index.
>
> Sounds fine to me.  Any chance of seeing this in 6.4?

I could add this (and FOREIGN key implementation) before
11-13 Sep... But not the ALTER TABLE ADD/DROP CONSTRAINT
stuff (ok for Entry SQL).
But we are in beta...

Comments?

> Nope, pg_index is fine by me.  Now, once we have this, how do we find
> the index for a particular attribute?  I can't seem to figure out the
> relationship between pg_attribute and pg_index.  The chart in the docs
> suggests that indkey is the relation but I can't see any useful info
> there for joining the tables.

pg_index:
    indrelid - oid of indexed relation
    indkey   - up to the 8 attnums

pg_attribute:
    attrelid - oid of relation
    attnum   - ...

Without outer join you have to query pg_attribute for each
valid attnum from pg_index->indkey -:(

Vadim