Re: [HACKERS] Adding PRIMARY KEY info

D'Arcy J.M. Cain wrote:
>
> Thus spake Vadim Mikheev
> > Imho, indices should be used/created for FOREIGN keys and so pg_index
> > is good place for both PRIMARY and FOREIGN keys infos.
>
> Are you sure?  I don't know about implementing it but it seems more
> like an attribute thing rather than an index thing.  Certainly from a
> database design viewpoint you want to refer to the fields, not the
> index on them.  If you put it into the index then you have to do
> an extra join to get the information.
>
> Perhaps you have to do the extra join anyway for other purposes so it
> may not matter.  All I want is to be able to be able to extract the
> field that the designer specified as the key.  As long as I can design
> a select statement that gives me that I don't much care how it is
> implemented.  I'll cache the information anyway so it won't have a
> huge impact on my programs.

First, let me note that you have to add int28 field to pg_class,
not just oid field, to know what attributeS are in primary key
(we support multi-attribute primary keys).
This could be done...
But what about foreign and unique (!) keys ?
There may be _many_ foreign/unique keys defined for one table!
And so foreign/unique keys info have to be stored somewhere else,
not in pg_class.

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.

I agreed that indices are just implementation...
If you don't like to store key infos in pg_index then
new pg_key relation have to be added...

Comments ?

Vadim