Re: ERROR: duplicate key violates unique constraint (SOLVED) - Mailing list 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_categoryWHERE               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!
>
>  
>