diff options
Diffstat (limited to 'doc/TODO.detail/primary')
| -rw-r--r-- | doc/TODO.detail/primary | 159 |
1 files changed, 159 insertions, 0 deletions
diff --git a/doc/TODO.detail/primary b/doc/TODO.detail/primary new file mode 100644 index 00000000000..a5bd4cf2647 --- /dev/null +++ b/doc/TODO.detail/primary @@ -0,0 +1,159 @@ +From owner-pgsql-hackers@hub.org Fri Sep 4 00:47:06 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.8.5/8.8.5) with ESMTP id AAA01047 + for <maillist@candle.pha.pa.us>; Fri, 4 Sep 1998 00:47:05 -0400 (EDT) +Received: from hub.org (hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id XAA02044 for <maillist@candle.pha.pa.us>; Thu, 3 Sep 1998 23:11:07 -0400 (EDT) +Received: from localhost (majordom@localhost) by hub.org (8.8.8/8.7.5) with SMTP id XAA27418; Thu, 3 Sep 1998 23:06:16 -0400 (EDT) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 03 Sep 1998 23:04:11 +0000 (EDT) +Received: (from majordom@localhost) by hub.org (8.8.8/8.7.5) id XAA27185 for pgsql-hackers-outgoing; Thu, 3 Sep 1998 23:04:09 -0400 (EDT) +Received: from dune.krs.ru (dune.krs.ru [195.161.16.38]) by hub.org (8.8.8/8.7.5) with ESMTP id XAA27169 for <hackers@postgreSQL.org>; Thu, 3 Sep 1998 23:03:59 -0400 (EDT) +Received: from krs.ru (localhost.krs.ru [127.0.0.1]) + by dune.krs.ru (8.8.8/8.8.8) with ESMTP id LAA10059; + Fri, 4 Sep 1998 11:03:00 +0800 (KRSS) + (envelope-from vadim@krs.ru) +Message-ID: <35EF5864.E5142D35@krs.ru> +Date: Fri, 04 Sep 1998 11:03:00 +0800 +From: Vadim Mikheev <vadim@krs.ru> +Organization: OJSC Rostelecom (Krasnoyarsk) +X-Mailer: Mozilla 4.05 [en] (X11; I; FreeBSD 2.2.6-RELEASE i386) +MIME-Version: 1.0 +To: "D'Arcy J.M. Cain" <darcy@druid.net> +CC: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu>, hackers@postgreSQL.org +Subject: Re: [HACKERS] Adding PRIMARY KEY info +References: <m0zEaoV-00006JC@druid.net> +Content-Type: text/plain; charset=us-ascii +Content-Transfer-Encoding: 7bit +Sender: owner-pgsql-hackers@hub.org +Precedence: bulk +Status: RO + +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 + + +From owner-pgsql-hackers@hub.org Sat Sep 5 02:01:13 1998 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.8.5/8.8.5) with ESMTP id CAA14437 + for <maillist@candle.pha.pa.us>; Sat, 5 Sep 1998 02:01:11 -0400 (EDT) +Received: from hub.org (hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.1 $) with ESMTP id BAA09928 for <maillist@candle.pha.pa.us>; Sat, 5 Sep 1998 01:48:32 -0400 (EDT) +Received: from localhost (majordom@localhost) by hub.org (8.8.8/8.7.5) with SMTP id BAA18282; Sat, 5 Sep 1998 01:43:16 -0400 (EDT) +Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Sat, 05 Sep 1998 01:41:40 +0000 (EDT) +Received: (from majordom@localhost) by hub.org (8.8.8/8.7.5) id BAA18241 for pgsql-hackers-outgoing; Sat, 5 Sep 1998 01:41:38 -0400 (EDT) +Received: from dune.krs.ru (dune.krs.ru [195.161.16.38]) by hub.org (8.8.8/8.7.5) with ESMTP id BAA18211; Sat, 5 Sep 1998 01:41:21 -0400 (EDT) +Received: from krs.ru (localhost.krs.ru [127.0.0.1]) + by dune.krs.ru (8.8.8/8.8.8) with ESMTP id NAA20555; + Sat, 5 Sep 1998 13:40:44 +0800 (KRSS) + (envelope-from vadim@krs.ru) +Message-ID: <35F0CEDB.AD721090@krs.ru> +Date: Sat, 05 Sep 1998 13:40:43 +0800 +From: Vadim Mikheev <vadim@krs.ru> +Organization: OJSC Rostelecom (Krasnoyarsk) +X-Mailer: Mozilla 4.05 [en] (X11; I; FreeBSD 2.2.6-RELEASE i386) +MIME-Version: 1.0 +To: "D'Arcy J.M. Cain" <darcy@druid.net> +CC: hackers@postgreSQL.org, pgsql-core@postgreSQL.org +Subject: Re: [HACKERS] Adding PRIMARY KEY info +References: <m0zEvLK-00006FC@druid.net> +Content-Type: text/plain; charset=us-ascii +Content-Transfer-Encoding: 7bit +Sender: owner-pgsql-hackers@hub.org +Precedence: bulk +Status: ROr + +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 + + |