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 ; 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 ; 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 ; 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 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" CC: "Thomas G. Lockhart" , hackers@postgreSQL.org Subject: Re: [HACKERS] Adding PRIMARY KEY info References: 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 ; 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 ; 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 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" CC: hackers@postgreSQL.org, pgsql-core@postgreSQL.org Subject: Re: [HACKERS] Adding PRIMARY KEY info References: 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