From: Josh Berkus <josh@agliodbs.com>
To: Khairul Azmi <mie@mimos.my>
To: pgsql-sql@postgresql.org
Subject: Re: ERROR: duplicate key violates unique constraint
Date: Fri, 4 Jun 2004 10:05:13 -0700
Message-ID: <200406041005.13657.josh@agliodbs.com> (raw)
In-Reply-To: <40C02D88.3000209@mimos.my>
References: <40C02D88.3000209@mimos.my>
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
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: josh@agliodbs.com, mie@mimos.my
Subject: Re: ERROR: duplicate key violates unique constraint
In-Reply-To: <200406041005.13657.josh@agliodbs.com>
* 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