[Snort-users] How to create the DB indices with postgresql
dl at ...6187...
Thu Jun 27 07:06:03 EDT 2002
Hutchinson, Andrew wrote on Thu, Jun 27, 2002 at 08:05:45AM -0500:
> 1. Your syntax on the CREATE INDEX statement looks good. The name of
> the index should not matter - however, I usually use a notation that
> will let me know that that entity is an index (something like
> "idx_event_timestamp" or "ev_ts_idx" or something else that has meaning
> to you and will be decipherable for others).
Thanks. Ok. I've looked in to the ACID source and found the
names of at least two of the indexes (timestamp_index and
signature_index), so I used them, and named the other indexes
> 2. Without the error messages you are receiving regarding UNIQUE keys,
> it's difficult to say what is going on. If you try to specify a unique
> key on a column that contains duplicate values, or try to insert a
> duplicate value into a unique column, postgresql will raise an error
> condition. In this specific instance (event.timestamp) I would NOT
> specify UNIQUE, because it is very possible that two events can have the
> same timestamp.
As I said, I didn't specify UNIQUE anywhere, but I had a look
into my pgsql error log. The messages are like this:
ERROR: Cannot insert a duplicate key into unique index sig_reference_pkey
ERROR: Cannot insert a duplicate key into unique index acid_event_pkey
These indexes are created implicity on creation of the tables:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'sig_reference_pkey' for table 'sig_reference'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'acid_event_pkey' for table 'acid_event'
So, these have probably nothing to do with the manually created indexes.
Sorry for not checking this before asking. :-/
> 3. I don't use acid, but I would assume from the fields referenced by
> "acid_ag_alert.ag_sid + acid_ag_alert.ag_cid" that they are referring to
> the creation of a compound index. This would be accomplished like this:
> CREATE UNIQUE INDEX idx_ag_sid_cid ON
Ok, thanks, I try that. It seems to work, such that the
statement is accepted. :))
> I speculate that UNIQUE is ok in this instance b/c the cid.sid combo
> should be unique to each event (can't have the safe event id from the
> same sensor twice!).
Hmm ok, I did not use unique here either. Is there any
benefit or drawback (not) using UNIQUE, even if it would
> One caveat is to always be certain to use the whole compound index if
> you write your own sql statements. Referencing a single key of a
I guess I have to trust ACID here. I did not craft any SQL
statements by hand. But thanks for the hint.
> Hope that this helps,
Yes, thanks. :)
IRCnet: Mr-Spock - ceterum censeo Microsoftinem esse delendam -
*Daniel Lang * dl at ...6187... * +49 89 289 25735 * http://www.leo.org/~dl/*
More information about the Snort-users