pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: duplicate key violates unique constraint
6+ messages / 5 participants
[nested] [flat]
* ERROR: duplicate key violates unique constraint
@ 2004-06-04 08:06 Khairul Azmi <mie@mimos.my>
0 siblings, 1 reply; 6+ messages in thread
From: Khairul Azmi @ 2004-06-04 08:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: duplicate key violates unique constraint
@ 2004-06-04 17:05 Josh Berkus <josh@agliodbs.com>
parent: Khairul Azmi <mie@mimos.my>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2004-06-04 17:05 UTC (permalink / raw)
To: Khairul Azmi <mie@mimos.my>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: duplicate key violates unique constraint (SOLVED)
@ 2004-06-07 03:22 Khairul Azmi <mie@mimos.my>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Khairul Azmi @ 2004-06-07 03:22 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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_category WHERE
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!
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* ERROR: duplicate key violates unique constraint
@ 2007-03-20 09:11 remco lengers <pgsql@lengers.com>
0 siblings, 2 replies; 6+ messages in thread
From: remco lengers @ 2007-03-20 09:11 UTC (permalink / raw)
To: pgsql-sql
Hi List,
Its been a while since I worked with databases and I am running into the
following which I have not been able to find the root cause for:
I have the follow SQL statement:
INSERT INTO reference VALUES (DEFAULT,'123','2',1);
Which generates the following error:
"ERROR: duplicate key violates unique constraint "reference_pkey""
And the table definition looks like:
CREATE TABLE reference (
referencelist_nr serial unique,
reference_text varchar(40) NOT NULL,
reference_type integer NOT NULL,
Topic_Id integer NOT NULL,
PRIMARY KEY (referencelist_nr),
FOREIGN KEY (Topic_Id) REFERENCES Topic (Topic_Id),
FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr)
);
It seems to me for some reason "DEFAULT" doesn't select the next SERIAL.
If I run:
INSERT INTO reference VALUES (14,'123','2',1);
14 being the next free integer it works fine.....
I have been adding data with "COPY" into this table....does that break
something?
Your help/tips/insights are appreciated.
Solaris 10/Postgresql 8.1.4
Regards,
..Remco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: duplicate key violates unique constraint
@ 2007-03-20 09:20 Bart Degryse <Bart.Degryse@indicator.be>
parent: remco lengers <pgsql@lengers.com>
1 sibling, 0 replies; 6+ messages in thread
From: Bart Degryse @ 2007-03-20 09:20 UTC (permalink / raw)
To: remco lengers <pgsql@lengers.com>; pgsql-sql
Use
INSERT INTO reference(reference_text, reference_type, Topic_Id) VALUES (DEFAULT,'123','2',1);
instead.
Since referencelist_nr is a serial PostgreSQL will provide the next free integer itself.
>>> "remco lengers" <pgsql@lengers.com> 2007-03-20 10:11 >>>
Hi List,
Its been a while since I worked with databases and I am running into the following which I have not been able to find the root cause for:
I have the follow SQL statement:
INSERT INTO reference VALUES (DEFAULT,'123','2',1);
Which generates the following error:
"ERROR: duplicate key violates unique constraint "reference_pkey""
And the table definition looks like:
CREATE TABLE reference (
referencelist_nr serial unique,
reference_text varchar(40) NOT NULL,
reference_type integer NOT NULL,
Topic_Id integer NOT NULL,
PRIMARY KEY (referencelist_nr),
FOREIGN KEY (Topic_Id) REFERENCES Topic (Topic_Id),
FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr)
);
It seems to me for some reason "DEFAULT" doesn't select the next SERIAL.
If I run:
INSERT INTO reference VALUES (14,'123','2',1);
14 being the next free integer it works fine.....
I have been adding data with "COPY" into this table....does that break something?
Your help/tips/insights are appreciated.
Solaris 10/Postgresql 8.1.4
Regards,
..Remco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: duplicate key violates unique constraint
@ 2007-03-20 09:27 Richard Huxton <dev@archonet.com>
parent: remco lengers <pgsql@lengers.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2007-03-20 09:27 UTC (permalink / raw)
To: remco lengers <pgsql@lengers.com>; +Cc: pgsql-sql
remco lengers wrote:
> Hi List,
>
> Its been a while since I worked with databases and I am running into the
> following which I have not been able to find the root cause for:
>
> I have the follow SQL statement:
>
> INSERT INTO reference VALUES (DEFAULT,'123','2',1);
>
> Which generates the following error:
>
> "ERROR: duplicate key violates unique constraint "reference_pkey""
>
> And the table definition looks like:
>
> CREATE TABLE reference (
> referencelist_nr serial unique,
> reference_text varchar(40) NOT NULL,
> reference_type integer NOT NULL,
> Topic_Id integer NOT NULL,
> PRIMARY KEY (referencelist_nr),
> FOREIGN KEY (Topic_Id) REFERENCES Topic (Topic_Id),
> FOREIGN KEY (reference_type) REFERENCES reference_type (reference_type_nr)
> );
>
> It seems to me for some reason "DEFAULT" doesn't select the next SERIAL.
It does, but if you've been manually setting that value then the SERIAL
doesn't know about it. Sequences are fast and light, but they can be so
because they don't have to check the table.
> I have been adding data with "COPY" into this table....does that break
> something?
There you go. After a bulk load you'll want to run something like:
SELECT setval('reference_referencelist_nr_seq', (SELECT
max(referencelist_nr)+1 FROM reference));
Have a look at pg_dump to see how it does this sort of thing.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-03-20 09:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-04 08:06 ERROR: duplicate key violates unique constraint Khairul Azmi <mie@mimos.my>
2004-06-04 17:05 ` Josh Berkus <josh@agliodbs.com>
2004-06-07 03:22 ` Re: ERROR: duplicate key violates unique constraint (SOLVED) Khairul Azmi <mie@mimos.my>
2007-03-20 09:11 ERROR: duplicate key violates unique constraint remco lengers <pgsql@lengers.com>
2007-03-20 09:20 ` Re: ERROR: duplicate key violates unique constraint Bart Degryse <Bart.Degryse@indicator.be>
2007-03-20 09:27 ` Re: ERROR: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
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