From: | Ryan Bradetich <rbradetich(at)uswest(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Questions about indexes? |
Date: | 2003-02-17 07:42:26 |
Message-ID: | 1045467746.30629.47.camel@beavis.ybsoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Olleg Samoylov | 2003-02-17 09:50:31 | Re: function to return pg_user.usesysid |
Previous Message | Tom Lane | 2003-02-17 07:15:27 | Re: Questions about indexes? |