Re: How to create the DB indices with postgresql

[Daniel Lang <dl () leo org>]

Dear Andrew,

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
alike.

> 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
> acid_ag_alert(ag_sid,ag_cid);
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
be possible?


> 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. :)

Best regards, 
 Daniel
-- 
IRCnet: Mr-Spock         - ceterum censeo Microsoftinem esse delendam -  
*Daniel Lang * dl () leo org * +49 89 289 25735 * http://www.leo.org/~dl/*


-------------------------------------------------------
Sponsored by:
ThinkGeek at http://www.ThinkGeek.com/
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users