Message-ID: <40C02D88.3000209@mimos.my>
Date: Fri, 04 Jun 2004 16:06:32 +0800
From: Khairul Azmi <mie@mimos.my>
User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: ERROR:  duplicate key violates unique constraint
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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_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