[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] PROBLEM with inserting a duplicate key into unique
From: "Henshall, Stuart - Design & Print" <SHenshall () westcountry-design-print ! co ! uk>
Date: 2002-10-31 13:26:16
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2652.35">
<TITLE>RE: [GENERAL] PROBLEM with inserting a duplicate key into unique index \
...</TITLE> </HEAD>
<BODY>
<P><FONT SIZE=2>juleni wrote:</FONT>
<BR><FONT SIZE=2>> Hello,</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> I have a following (written below) plsql function \
which adds records</FONT> <BR><FONT SIZE=2>> into the table \
"contact_place" and returns current inserted record ID</FONT> <BR><FONT \
SIZE=2>> as a result (this is provided by function \
"getActualID(<seq_name>)").</FONT> <BR><FONT SIZE=2>> \
Problem is, that sometimes is during inserting following error</FONT> <BR><FONT \
SIZE=2>> occured: </FONT> <BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> java.sql.SQLException:</FONT>
<BR><FONT SIZE=2>> Cannot insert a duplicate key into unique index</FONT>
<BR><FONT SIZE=2>> contact_place_id_contact_key</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Primary key is of type SERIAL and the sequence \
shoul be set</FONT> <BR><FONT SIZE=2>> automatically during inserting. After that \
I only find out this</FONT> <BR><FONT SIZE=2>> current sequence (this is provided \
by function getActualID(...)).</FONT> <BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Mostly are inserted records written currently, but \
sometimes is</FONT> <BR><FONT SIZE=2>> error occured. I really don't know, what \
I'm doing wrong.</FONT> <BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Can anybody help me with my problem?</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Thanks in advance,</FONT>
<BR><FONT SIZE=2>> Julian.</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Mailto : legeny@softhome.net</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> \
==============================================================</FONT> <BR><FONT \
SIZE=2>> ================= Create table contact_place (</FONT> <BR><FONT \
SIZE=2>> id_contact Serial NOT \
NULL UNIQUE ,</FONT> <BR><FONT \
SIZE=2>> fkid_contact_type Integer \
NOT NULL,</FONT> <BR><FONT \
SIZE=2>> fkid_location Integer NOT \
NULL,</FONT> <BR><FONT SIZE=2>> \
fkid_company Integer NOT NULL,</FONT> <BR><FONT \
SIZE=2>> street Varchar(50) NOT \
NULL,</FONT> <BR><FONT SIZE=2>> \
city Varchar(30) NOT NULL,</FONT> <BR><FONT \
SIZE=2>> zip Numeric(6,0) NOT \
NULL,</FONT> <BR><FONT SIZE=2>> primary key</FONT>
<BR><FONT SIZE=2>> (id_contact,fkid_contact_type,fkid_location,fkid_company) ); \
</FONT> <BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> \
==============================================================</FONT> <BR><FONT \
SIZE=2>> ================= CREATE OR REPLACE FUNCTION</FONT> <BR><FONT SIZE=2>> \
add_Contact_Place(INTEGER, INTEGER,INTEGER,VARCHAR(50) </FONT> <BR><FONT SIZE=2>> \
</FONT> <BR><FONT SIZE=2>> ,VARCHAR(30),NUMERIC(6,0))</FONT>
<BR><FONT SIZE=2>> RETURNS INTEGER AS '</FONT>
<BR><FONT SIZE=2>> DECLARE</FONT>
<BR><FONT SIZE=2>> pContactType ALIAS FOR $1;</FONT>
<BR><FONT SIZE=2>> pLocation ALIAS FOR $2;</FONT>
<BR><FONT SIZE=2>> pCompanyID ALIAS FOR $3;</FONT>
<BR><FONT SIZE=2>> pStreet ALIAS FOR $4;</FONT>
<BR><FONT SIZE=2>> pCity ALIAS FOR $5;</FONT>
<BR><FONT SIZE=2>> pZIP ALIAS FOR $6;</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> BEGIN</FONT>
<BR><FONT SIZE=2>> LOCK TABLE contact_place IN EXCLUSIVE MODE;</FONT>
<BR><FONT SIZE=2>> INSERT INTO contact_place \
(fkid_contact_type,</FONT> <BR><FONT SIZE=2>> fkid_location, fkid_company, street, \
city, zip)</FONT> <BR><FONT \
SIZE=2>> VALUES \
(pContactType, pLocation, pCompanyID, pStreet, pCity,</FONT> <BR><FONT \
SIZE=2>> pZIP); RETURN getActualID(''contact_place_id_contact_seq''); \
END</FONT> <BR><FONT SIZE=2>> ' LANGUAGE 'plpgsql';</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> \
==============================================================</FONT> <BR><FONT \
SIZE=2>> ================= </FONT> <BR><FONT SIZE=2>> -- Function which return \
current ID value from the specified sequence</FONT> <BR><FONT SIZE=2>> CREATE OR \
REPLACE FUNCTION getActualID(text) RETURNS BIGINT AS ' </FONT> <BR><FONT \
SIZE=2>> SELECT CURRVAL($1); ' LANGUAGE 'sql';</FONT> <BR><FONT SIZE=2>> \
</FONT> <BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>I can't see why your getting this error off hand, but maybe try like \
this to see if it solves the problem:</FONT> <BR><FONT SIZE=2>CREATE OR REPLACE \
FUNCTION add_Contact_Place(INTEGER, INTEGER,INTEGER,VARCHAR(50)</FONT> <BR><FONT \
SIZE=2> & \
nbsp; &nb \
sp; \
,VARCHAR(30),NUMERIC(6,0))</FONT> <BR><FONT SIZE=2>RETURNS INTEGER AS '</FONT>
<BR><FONT SIZE=2>DECLARE</FONT>
<BR><FONT SIZE=2> pContactType ALIAS FOR $1;</FONT>
<BR><FONT SIZE=2> pLocation ALIAS FOR $2;</FONT>
<BR><FONT SIZE=2> pCompanyID ALIAS FOR $3;</FONT>
<BR><FONT SIZE=2> pStreet ALIAS FOR $4;</FONT>
<BR><FONT SIZE=2> pCity ALIAS FOR $5;</FONT>
<BR><FONT SIZE=2> pZIP ALIAS FOR $6;</FONT>
<BR><FONT SIZE=2> fid_contact int4;</FONT>
<BR><FONT SIZE=2>BEGIN</FONT>
<BR><FONT SIZE=2> \
fid_contact=nextval(''contact_place_id_contact_seq''); \
</FONT> <BR><FONT SIZE=2> INSERT INTO contact_place \
(id_contact,fkid_contact_type, fkid_location, fkid_company, street, city, zip)</FONT> \
<BR><FONT SIZE=2> VALUES \
(fid_contact,pContactType, pLocation, pCompanyID, pStreet, pCity, pZIP);</FONT> \
<BR><FONT SIZE=2> RETURN fid_contact;</FONT> <BR><FONT SIZE=2>END</FONT>
<BR><FONT SIZE=2>' LANGUAGE 'plpgsql';</FONT>
</P>
<P><FONT SIZE=2>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....</FONT></P>
<P><FONT SIZE=2>hth,</FONT>
<BR><FONT SIZE=2>- Stuart</FONT>
</P>
</BODY>
</HTML>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic