1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
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.2 $) 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.2 $) 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
|
