Thread: 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

Thus spake Vadim Mikheev
> D'Arcy J.M. Cain wrote:
> > 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 -:(

Hmmm.  Well, to start with, perhaps I can specify that the functions
only work with simple keys.  Do we even support complex primary keys?
Anyway, if I do that then the following should work with indisunique
replaced by indisprimary.

SELECT pg_class.relname, pg_attribute.attname
    FROM pg_class, pg_attribute, pg_index
    WHERE pg_class.oid = pg_attribute.attrelid AND
        pg_class.oid = pg_index.indrelid AND
        pg_index.indkey[0] = pg_attribute.attnum AND
        pg_index.indisunique = 't';

In fact, the above would work if I could assume that each table had only
one unique index but I think that that's too much of a restriction.  I
hope you can add that flag for this release.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

> 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?

Now that you have fixed the index problem, I can focus on the other open
issues.  You were asking about adding foreign keys.  This, of course, is
a BIG item for our users, and would be nice to have it.  Not sure how
that is going to merge into the beta, but we are allowing Jan to add
rewrite changes during the beta, and perhaps Marc will allow foreign
keys too, since it is such a HOT item.  We may be adding the IP type
during beta, which is hot too.

I believe Marc said he was unsure of novices adding stuff during beta,
but people who have proven responsible/responsive are OK.  (After the
index thing, I am not sure if I am still on that list, but I don't have
anything to add during beta anyway.  I did all my damage before the
beta.  :-))

I say, go ahead and work on it if you want to, and if it looks good, we
can add it, if not, it can be added just after the final release.
Either way, the code is not going to change that much from now until
final, so if you hold on it or you don't finish, you can always add the
code later.


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)