Message-ID: <40C3DF8D.9090105@mimos.my>
Date: Mon, 07 Jun 2004 11:22:53 +0800
From: Khairul Azmi <mie@mimos.my>
User-Agent: Mozilla Thunderbird 0.5 (X11/20040519)
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: ERROR:  duplicate key violates unique constraint (SOLVED)
References: <40C02D88.3000209@mimos.my> <200406041005.13657.josh@agliodbs.com>
In-Reply-To: <200406041005.13657.josh@agliodbs.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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