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(at)softhome(dot)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