Thread: ERROR: duplicate key violates unique constraint
Need help on this problem. I've created two functions. The function
should perform a few sql processes. The problem is I got different
results when I call the function and when I manually run the sql command.
I'm using postgresql 7.4.2.
The two functions involved are
CREATE TABLE sensor_signature ( sid INTEGER, rh_sign_id INTEGER,
PRIMARY KEY (sid,rh_sign_id));
CREATE TABLE rule_header ( rh_sign_id INTEGER, rh_status INTEGER,
rh_action VARCHAR(6), proto VARCHAR(10), source_ip
VARCHAR(255), source_port VARCHAR(64), dest_ip VARCHAR(255),
dest_port VARCHAR(64), dir_operator VARCHAR(64),
category VARCHAR(64), rh_revision INTEGER, timestamp
timestamp, rh_ord INTEGER, PRIMARY KEY (rh_sign_id));
And my functions are
CREATE OR REPLACE FUNCTION update_sen_sig (integer, varchar)
RETURNS integer AS '
DECLARE sign_id alias FOR $1; category alias FOR $2; temp_sid integer; temp_category varchar; T1Cursor
refcursor;
BEGIN select * into temp_category from get_root_path(category); OPEN T1Cursor FOR SELECT sid FROM
conf_categoryWHERE category_name like temp_category and category_status=1; LOOP FETCH T1Cursor INTO
temp_sid; EXIT WHEN NOT FOUND; INSERT INTO sensor_signature VALUES (temp_sid, sign_id); RAISE NOTICE ''
INSERTINTO sensor_signature VALUES % % '',
temp_sid,sign_id; END LOOP; CLOSE T1Cursor;
--- COMMIT;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION rule_header_add
(integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer)
RETURNS integer AS '
DECLARE var_f0 alias FOR $1; -- rh_sign_id var_f1 alias FOR $2; -- rh_status var_f2 alias FOR $3; --
rh_action var_f3 alias FOR $4; -- proto var_f4 alias FOR $5; -- source_ip var_f5 alias FOR $6; --
source_port var_f6 alias FOR $7; -- dest_ip var_f7 alias FOR $8; -- dest_port var_f8 alias FOR $9; --
dir_operator var_f9 alias FOR $10; -- category var_f10 alias FOR $11; -- rh_revision var_f11 alias FOR $12;
-- rh_ord curtime timestamp; var_temp_RH_ORD integer;
BEGIN curtime := current_timestamp; SELECT rh_ord INTO var_temp_RH_ORD FROM rule_header WHERE rh_sign_id =
var_f0; IF NOT FOUND THEN INSERT INTO rule_header VALUES
(var_f0,var_f1,var_f2,var_f3,var_f4,var_f5,var_f6,var_f7,var_f8,var_f9,var_f10,curtime,var_f11);
RAISE NOTICE '' INSERT INTO rule_header VALUES % % % % % % % % %
% % % '',
var_f0,var_f1,var_f2,var_f3,var_f4,var_f5,var_f6,var_f7,var_f8,var_f9,var_f10,curtime,var_f11;
--- select update_sen_sig(var_f0,var_f9); perform update_sen_sig(var_f0,var_f9); RETURN 0; ELSE
UPDATE rule_header SET rh_status=var_f1, rh_action=var_f2,
proto=var_f3,source_ip=var_f4, source_port=var_f5, dest_ip=var_f6,
dest_port=var_f7, dir_operator=var_f8, category=var_f9,
rh_revision=var_f10, timestamp=curtime, rh_ord=var_temp_RH_ORD where
rh_sign_id=var_f0; DELETE FROM rule_option where rh_sign_id=var_f0; RETURN 1; END IF;
END;
' LANGUAGE 'plpgsql';
Issue is
cews=> select
rule_header_add(999,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules',3,0);
NOTICE: INSERT INTO rule_header VALUES 999 1 alert ip $EXTERNAL_NET any
$HOME_NET any -> dos.rules 3 2004-06-04 15:21:30.448633
NOTICE: INSERT INTO sensor_signature VALUES -1 999
CONTEXT: PL/pgSQL function "rule_header_add" line 26 at perform
ERROR: duplicate key violates unique constraint "sensor_signature_pkey"
CONTEXT: PL/pgSQL function "update_sen_sig" line 16 at SQL statement
PL/pgSQL function "rule_header_add" line 26 at perform
I thought it might be caused by duplicated data. But ...
cews=> insert into rule_header values
(268,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules',3,current_timestamp,0);
INSERT 29393 1
And
cews=> insert into sensor_signature values (-1,268);
INSERT 29394 1
That commands work perfectly. Could somebody tell me why and how to
solve this. Thanks.
Azmi
Khairul, > Need help on this problem. I've created two functions. The function > should perform a few sql processes. The problem is I got different > results when I call the function and when I manually run the sql command. > > I'm using postgresql 7.4.2. Hmmm ... that's odd. I remember getting this issue early in the 7.2 series but not since. I'd guess that you're missing something in your function, like the transposition of two fields or an unterminated loop. You've made that likely because: > RETURNS integer AS ' > DECLARE > var_f0 alias FOR $1; -- rh_sign_id > var_f1 alias FOR $2; -- rh_status > var_f2 alias FOR $3; -- rh_action > var_f3 alias FOR $4; -- proto ... this is a really bad way of dealing with function variables; I certainly can't parse the rest of the function and tell if you've accidentally swapped a var_f3 for a var_f4. I'd strongly suggest naming your variables clearly, like, for example, calling it "v_proto" instead of "var_f3". This is "programming 101". > Issue is > cews=> select > rule_header_add(999,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any', >'->','dos.rules',3,0); NOTICE: INSERT INTO rule_header VALUES 999 1 alert > ip $EXTERNAL_NET any $HOME_NET any -> dos.rules 3 2004-06-04 > 15:21:30.448633 > NOTICE: INSERT INTO sensor_signature VALUES -1 999 > CONTEXT: PL/pgSQL function "rule_header_add" line 26 at perform > ERROR: duplicate key violates unique constraint "sensor_signature_pkey" > CONTEXT: PL/pgSQL function "update_sen_sig" line 16 at SQL statement > PL/pgSQL function "rule_header_add" line 26 at perform > > I thought it might be caused by duplicated data. But ... > cews=> insert into rule_header values > (268,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules >',3,current_timestamp,0); INSERT 29393 1 > > And > cews=> insert into sensor_signature values (-1,268); > INSERT 29394 1 This isn't the same id you tested with the function. Mind running the *exact same values* with both command line and function? Also, I notice that update_sen_sig makes use of a cursor and a loop. Best guess is that the cursor isn't returning what you think it is, and is looping several times ... thus attempting to insert the same value several times. Good luck! -- Josh Berkus Aglio Database Solutions San Francisco
The problem is solved. It is a silly mistake actually. The line SELECT sid FROM conf_category WHERE category_name like temp_category and category_status=1; returns duplicated values. Variable temp_category would be '%dos.rules' and there are entries 'dos.rules' and 'ddos.rules' in table conf_category which I did not notice until today. So I add the keyword DISTINCT and it runs perfectly. select * into temp_category from get_root_path(category); OPEN T1Cursor FOR SELECT DISTINCT sid FROM conf_categoryWHERE category_name like temp_category and category_status=1; Josh Berkus wrote: >Khairul, > > > >>Need help on this problem. I've created two functions. The function >>should perform a few sql processes. The problem is I got different >>results when I call the function and when I manually run the sql command. >> >>I'm using postgresql 7.4.2. >> >> > >Hmmm ... that's odd. I remember getting this issue early in the 7.2 series >but not since. I'd guess that you're missing something in your function, >like the transposition of two fields or an unterminated loop. You've made >that likely because: > > > >>RETURNS integer AS ' >>DECLARE >> var_f0 alias FOR $1; -- rh_sign_id >> var_f1 alias FOR $2; -- rh_status >> var_f2 alias FOR $3; -- rh_action >> var_f3 alias FOR $4; -- proto >> >> > >... this is a really bad way of dealing with function variables; I certainly >can't parse the rest of the function and tell if you've accidentally swapped >a var_f3 for a var_f4. I'd strongly suggest naming your variables clearly, >like, for example, calling it "v_proto" instead of "var_f3". This is >"programming 101". > > > I am porting this application from ORACLE. So I try my best not to change the structure of the original codes. I guess it is still not a good excuse for the way I name the variable :). Anyway thanks Josh. >>Issue is >>cews=> select >>rule_header_add(999,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any', >>'->','dos.rules',3,0); NOTICE: INSERT INTO rule_header VALUES 999 1 alert >>ip $EXTERNAL_NET any $HOME_NET any -> dos.rules 3 2004-06-04 >>15:21:30.448633 >>NOTICE: INSERT INTO sensor_signature VALUES -1 999 >>CONTEXT: PL/pgSQL function "rule_header_add" line 26 at perform >>ERROR: duplicate key violates unique constraint "sensor_signature_pkey" >>CONTEXT: PL/pgSQL function "update_sen_sig" line 16 at SQL statement >>PL/pgSQL function "rule_header_add" line 26 at perform >> >>I thought it might be caused by duplicated data. But ... >>cews=> insert into rule_header values >>(268,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules >>',3,current_timestamp,0); INSERT 29393 1 >> >>And >>cews=> insert into sensor_signature values (-1,268); >>INSERT 29394 1 >> >> > >This isn't the same id you tested with the function. Mind running the *exact >same values* with both command line and function? > >Also, I notice that update_sen_sig makes use of a cursor and a loop. Best >guess is that the cursor isn't returning what you think it is, and is looping >several times ... thus attempting to insert the same value several times. > >Good luck! > > >