PROBLEM with inserting a duplicate key into unique index ... - Mailing list pgsql-general

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';