Thread: Re: PROBLEM with inserting a duplicate key into unique

juleni wrote:
> Hello,
>
>   I have a following (written below) plsql function which adds records
> into the table "contact_place" and returns current inserted record ID
> as a result (this is provided by function "getActualID(<seq_name>)").
>   Problem is, that sometimes is during inserting following error
> occured:
>
> java.sql.SQLException:
> Cannot insert a duplicate key into unique index
> contact_place_id_contact_key
>
>   Primary key is of type SERIAL and the sequence shoul be set
> automatically during inserting. After that I only find out this
> current sequence (this is provided by function getActualID(...)).
>
>   Mostly are inserted records written currently, but sometimes is
> error occured. I really don't know, what I'm doing wrong.
>
>   Can anybody help me with my problem?
>
>   Thanks in advance,
>   Julian.
>
>   Mailto : legeny@softhome.net
>
>
> ==============================================================
> ================= Create table  contact_place (
>         id_contact Serial NOT NULL UNIQUE ,
>         fkid_contact_type Integer NOT NULL,
>         fkid_location Integer NOT NULL,
>         fkid_company Integer NOT NULL,
>         street Varchar(50) NOT NULL,
>         city Varchar(30) NOT NULL,
>         zip Numeric(6,0) NOT NULL,
>  primary key
> (id_contact,fkid_contact_type,fkid_location,fkid_company) );
>
> ==============================================================
> ================= CREATE OR REPLACE FUNCTION
> add_Contact_Place(INTEGER, INTEGER,INTEGER,VARCHAR(50)
>
> ,VARCHAR(30),NUMERIC(6,0))
> RETURNS INTEGER AS '
> DECLARE
>   pContactType ALIAS FOR $1;
>   pLocation ALIAS FOR $2;
>   pCompanyID ALIAS FOR $3;
>   pStreet ALIAS FOR $4;
>   pCity ALIAS FOR $5;
>   pZIP ALIAS FOR $6;
>
> BEGIN
>   LOCK TABLE contact_place IN EXCLUSIVE MODE;
>   INSERT INTO contact_place (fkid_contact_type,
> fkid_location, fkid_company, street, city, zip)
>          VALUES (pContactType, pLocation, pCompanyID, pStreet, pCity,
>   pZIP); RETURN getActualID(''contact_place_id_contact_seq''); END
> ' LANGUAGE 'plpgsql';
>
> ==============================================================
> =================
> -- Function which return current ID value from the specified sequence
> CREATE OR REPLACE FUNCTION getActualID(text) RETURNS BIGINT AS ' 
> SELECT CURRVAL($1); ' LANGUAGE 'sql';
>
>
I can't see why your getting this error off hand, but maybe try like this to see if it solves the problem:
CREATE OR REPLACE FUNCTION add_Contact_Place(INTEGER, INTEGER,INTEGER,VARCHAR(50)
                                             ,VARCHAR(30),NUMERIC(6,0))
RETURNS INTEGER AS '
DECLARE
  pContactType ALIAS FOR $1;
  pLocation ALIAS FOR $2;
  pCompanyID ALIAS FOR $3;
  pStreet ALIAS FOR $4;
  pCity ALIAS FOR $5;
  pZIP ALIAS FOR $6;
  fid_contact int4;
BEGIN
  fid_contact=nextval(''contact_place_id_contact_seq'');       
  INSERT INTO contact_place (id_contact,fkid_contact_type, fkid_location, fkid_company, street, city, zip)
         VALUES (fid_contact,pContactType, pLocation, pCompanyID, pStreet, pCity, pZIP);
  RETURN fid_contact;
END
' LANGUAGE 'plpgsql';

I've also got rid of the table locking as this shouldn't make any difference either way. To be extra carefull you could put a select b4 the insert. Although if this is your only method of input into the table I can't see how its getting duplicate keys with your original either....

hth,
- Stuart