On 9/21/05, Michael L. Artz <dragon@xxxxxxxxxxxxx> wrote: > Tony Wasson wrote: > > >You can use the merge trigger below to do this. You'll need to add > >some code to update the count. You may also benefit from using the new > >constraint exclusion (table partitioning) in PostgreSQL 8.1. I am not > >sure if CE works against the inet datatype -- if not, try converting > >the IP to an integer. > > > > > > CE looked like it was just for parent/child relationships ... did I read > that right? I'm not sure how it applies. And the table partitioning > looks like its still on the todo list ... is that really the case? CE is available in the PostgreSQL 8.1 beta. I was thinking you might use it to slice up your data based on subnet. You can make it all fit in a single table, but splitting it up could help keep the indexes smaller. Are your SELECTs going to primarily base on source and destination IPs? You have the possibility of a massive amount of rows! Is there anything to be learned from large installations of snort using a Pg backend? > And as for getting data into the DB ... from earlier posts it sounded > like standard practice was to bulk load the new data into a temporary > table and then do an INSERT ... SELECT to load the data into the new > table. Is this still the case with the trigger, or can/should I just > COPY the data straight into the final database? And I assume that I > should *not* delete my indexes while I'm loading the table, since the > queries in the trigger can take advantage of them ... right? The initial load can be a COPY or a bunch of INSERTs and I'd suggest doing it without any triggers or indexes. Then build your indexes and add your triggers. The way I normally use this trigger is with INSERTs. You can use a COPY against it too. > Also, as a slight aside, has anyone created a data type for single IPs > that is essentially an integer (i.e. 4 bytes) that works with the > standard functions for INET? I've never seen a datatype that does this. Here are some functions to convert INET to INT8 and back. CREATE OR REPLACE FUNCTION inet_ntoa(int8) RETURNS inet AS ' --from http://www.snort.org/docs/snortdb/snortdb_faq.html#faq_b4 SELECT ( (($1>>24) & 255::int8) || ''.'' || (($1>>16) & 255::int8) || ''.'' || (($1>>8) & 255::int8) || ''.'' || ($1 & 255::int8) )::INET; ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION inet_aton(inet) RETURNS int8 AS ' -- this ignores any subnetting information --http://www.mcabee.org/lists/snort-users/Oct-01/msg00426.html SELECT ( (split_part($1::TEXT,''.'',1)::INT8*16777216) + (split_part($1::TEXT,''.'',2)::INT8*65536) + (split_part($1::TEXT,''.'',3)::INT8*256) + (split_part(split_part($1::TEXT,''.'',4),''/'',1))::INT8 )::INT8; ' LANGUAGE 'sql'; ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
