Hmm but if I exclude the src_host and dst_host from the aggregation the counts should be summarized over destination/source and only one entry should appear in the insert statements (for the primary key I defined in the sql-schema). Or in other words, if the aggregations match the primary key (including stamp_inserted) and the sql_refresh_time equals sql_history, there shouldn't be any duplicates. Am I wrong with this expectation?
aggregate[test]: tag, in_iface, out_iface, etype, src_mac, dst_mac, proto, tcpflags sql_refresh_time[test]: 60 sql_history[test]: 1m CONSTRAINT test_%d_%H_pk PRIMARY KEY (agent_id, etype, mac_src, mac_dst, tcp_flags, ip_proto, iface_in, iface_out, stamp_inserted) >On 03/24/2013 12:07:01 PM, Gsell Reto ([email protected]) wrote: >> Hi, >> >> I have a problem with duplicated key entries in postgresql 9.2. > > >> On every INSERT/COPY I get the following error in the db: >> >> ------------------ >> 2013-03-24 15:51:01 CET ERROR: duplicate key value violates unique >> constraint "test_24_15_pk" >> 2013-03-24 15:51:01 CET DETAIL: Key (agent_id, etype, mac_src, >> mac_dst, tcp_flags, ip_proto, iface_in, iface_out, stamp_inserted)= >> (9, >> 800 , xx:xx:xx:xx:xx:xx, xx:xx:xx:xx:xx:xx, 31, 6, 963, 962, >> 2013-03-24 15:50:00) already exists. >> 2013-03-24 15:51:01 CET CONTEXT: COPY test_24_15, line 2930: >> "2013-03-24 15:51:01,2013-03-24 15:50:00, xx:xx:xx:xx:xx:xx, >> xx:xx:xx:xx:xx:xx,800,833,897,0,1,9,1024,1..." >> 2013-03-24 15:51:01 CET STATEMENT: COPY test_24_15 (stamp_updated, >> stamp_inserted, mac_src, mac_dst, etype, iface_in, iface_out, >> tcp_flags, ip_proto, agent_id, packets, bytes) FROM STD > > >> CREATE TABLE test_%d_%H ( >> agent_id BIGINT NOT NULL DEFAULT 0, >> iface_in BIGINT NOT NULL DEFAULT 0, >> iface_out BIGINT NOT NULL DEFAULT 0, >> etype CHAR(5) NOT NULL DEFAULT '', >> mac_src macaddr NOT NULL DEFAULT '0:0:0:0:0:0', >> mac_dst macaddr NOT NULL DEFAULT '0:0:0:0:0:0', >> ip_proto SMALLINT NOT NULL DEFAULT 0, >> tcp_flags SMALLINT NOT NULL DEFAULT 0, >> packets BIGINT NOT NULL, >> bytes BIGINT NOT NULL, >> flows BIGINT NOT NULL DEFAULT 0, >> stamp_inserted timestamp without time zone NOT NULL DEFAULT >> '0001-01-01 00:00:00', >> stamp_updated timestamp without time zone, >> CONSTRAINT test_%d_%H_pk PRIMARY KEY (agent_id, etype, >> mac_src, mac_dst, tcp_flags, ip_proto, iface_in, iface_out, >> stamp_inserted) >> ); > >Well, yeah. I don't know what the resolution of your clock is, but if one >machine sends more than 1 packet per second to the same destination (or 1 >packet per minute, or hour, or 10th of a second, or whatever your clock >resolution >is) you're going to have a duplicate value. > >You might be better served to have some sort of an id column declared SERIAL >PRIMARY KEY. > > > > >Karl <[email protected]> >Free Software: "You don't pay back, you pay forward." > -- Robert A. Heinlein > > >_______________________________________________ >pmacct-discussion mailing list >http://www.pmacct.net/#mailinglists > _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
