Re: ERROR: duplicate key violates unique constraint (SOLVED)

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