Re: Questions about indexes?

On Mon, 2003-02-17 at 00:15, Tom Lane wrote:
> Ryan Bradetich <rbradetich(at)uswest(dot)net> writes:
> > On Sun, 2003-02-16 at 23:34, Tom Lane wrote:
> >> It's not real clear to me why you bother enforcing a constraint that the
> >> complete row be unique. Wouldn't a useful constraint be that the first
> >> three columns be unique?
>
> > The table holds system policy compliance data. The catagory is
> > basically the policy, and the anomaly is the detailed text explaining
> > why the system is out of compliance. So the anomaly data is important
> > (and often the reason why the key is unique).
>
> Well, sure the anomaly is important: it's the payload, the reason why
> you bother to have the table in the first place. But that doesn't mean
> it's part of the key. Generally the key would be the info you use to
> look up a particular anomaly text. In this example, it's not clear to
> me why you'd need/want two different anomaly texts entered for the same
> host_id and the same category at the same instant of time. ISTM there's
> something inadequate about your category column if you need that.

Ok, I understand what you are asking now :)

Let me make up a contrived example to show how the table is used.

host_id 1 = hosta.somewhere.com
host_id 2 = hostb.somewhere.com

The catagories are coded so (made up examples):
cat p101 = /etc/passwd check
cat f101 = filesystem check.

the table would look like:
1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user x has an invalid shell.
1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has an invalid shell.
1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has expired password.
2 | Mon Feb 17 00:34:24 MST 2003 | f101 | file /foo has improper owner.
etc...

So I do not need the anomaly to be part of the index, I only need it to

I agree with you, that I would not normally add the anomally to the
index, except for the unique row requirement. Thinking about it now,
maybe I should guarentee unique rows via a check constraint...

Thanks for making me think about this in a different way!

- Ryan

> regards, tom lane
--
Ryan Bradetich <rbradetich(at)uswest(dot)net>