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_category WHERE
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 '' INSERT INTO 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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
