Adding PRIMARY KEY info

I tried adding the PRIMARY KEY info to pg_class but the resulting data
base system simply dumps core everywhere.
The next step, of course, is to turn PRIMARY KEY statements into an
update to the class table to insert the oid of the KEY field. I
looked at parser/analyze.c but I think I have to look deeper.

I'm not sure this is easy (or possible :), but don't really know. The
multi-parse-tree expansions I've done in the parser do not try to take
results and use them as input to a different parse tree. I think if I
were trying this I'd look at triggers firing after an insert, but I'm
not sure how you would tie things together since the "primary key" is
implmented as just a unique index in the backend.

Hmm. How about having a "primary key" flag field in pg_index instead? We
could enforce integrity in the parser, since we can check that only one
primary key has been specified during the parsing. You might be able to
define a trigger on pg_index to update pg_class (if you still needed
that column) if the key field is set.

btw, if any of this is worth doing it is perhaps to allow us to
implement foreign keys later (assuming that primary and foreign keys are
related which is what I am recalling). How would we tie key information
together and enforce integrity? I haven't thought about it yet. Also,
Vadim was thinking about doing something for foreign keys, so we should
ask him where he was headed with that...

- Tom

I tried adding the PRIMARY KEY info to pg_class but the resulting data
base system simply dumps core everywhere. Can someone point me to the
areas that need to be addressed? Here's what I did. I added a field
called "relprimary" to pg_class in pg_class.h as type Oid then added
-1 to the pg_class entries in the appropriate place. I would also like
to figure out the oid of the keys for each system table if someone can
point me to those as well.

The next step, of course, is to turn PRIMARY KEY statements into an
update to the class table to insert the oid of the KEY field. I looked
at parser/analyze.c but I think I have to look deeper. Any help?

My recommendation is to find a seldom-used field in pg_class, like
relchecks, and find all instances of that in the code, and add code for
your new field in all those places. You need to assign the value in
call cases where you are inserting into pg_class, not just the initdb
stuff.

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

Thomas G. Lockhart wrote:

Hmm. How about having a "primary key" flag field in pg_index instead? We

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I like this.

could enforce integrity in the parser, since we can check that only one
primary key has been specified during the parsing. You might be able to
define a trigger on pg_index to update pg_class (if you still needed

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Impossible. Triggers are handled by executor, not by heap access
methods...

that column) if the key field is set.

btw, if any of this is worth doing it is perhaps to allow us to
implement foreign keys later (assuming that primary and foreign keys are
related which is what I am recalling). How would we tie key information
together and enforce integrity? I haven't thought about it yet. Also,
Vadim was thinking about doing something for foreign keys, so we should
ask him where he was headed with that...

Imho, indices should be used/created for FOREIGN keys and so pg_index
is good place for both PRIMARY and FOREIGN keys infos.

Vadim

Thus spake Bruce Momjian

I tried adding the PRIMARY KEY info to pg_class but the resulting data
base system simply dumps core everywhere. Can someone point me to the

My recommendation is to find a seldom-used field in pg_class, like
relchecks, and find all instances of that in the code, and add code for
your new field in all those places. You need to assign the value in
call cases where you are inserting into pg_class, not just the initdb
stuff.

Does this relate to the core dumping? Since there is so far no code
using this field, how would adding a field dump core? Can you see
anything in my description of what I did to account for this.

Your suggestion sounds good for the next step though. Thanks.

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

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.

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

Thus spake Bruce Momjian

I tried adding the PRIMARY KEY info to pg_class but the resulting data
base system simply dumps core everywhere. Can someone point me to the

My recommendation is to find a seldom-used field in pg_class, like
relchecks, and find all instances of that in the code, and add code for
your new field in all those places. You need to assign the value in
call cases where you are inserting into pg_class, not just the initdb
stuff.

Does this relate to the core dumping? Since there is so far no code
using this field, how would adding a field dump core? Can you see
anything in my description of what I did to account for this.

Your suggestion sounds good for the next step though. Thanks.

It is possible. Fields are accessed internally for copying and stuff,
though because it is a byvalue field, it should not matter.

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

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

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)