Thread: Adding PRIMARY KEY info
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?
--
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.
> 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