From: Khairul Azmi <mie@mimos.my>
To: pgsql-sql@postgresql.org
Subject: ERROR: duplicate key violates unique constraint
Date: Fri, 04 Jun 2004 16:06:32 +0800
Message-ID: <40C02D88.3000209@mimos.my> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mie@mimos.my
Subject: Re: ERROR: duplicate key violates unique constraint
In-Reply-To: <40C02D88.3000209@mimos.my>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox