Thread: duplicate key ? (fwd)
I didn't get any answer about my question posted yesterday.
please share your knowledge and wisdom with me, a poor s/w engineer.
---------- Forwarded message ----------
Date: Fri, 20 Oct 2000 21:30:27 +0900 (KST)
From: Sungchul Park <scpark@gen128.com>
To: pgsql-general@postgresql.org
Subject: [GENERAL] duplicate key ?
Please check following...
Fo2Me=# drop table dirinfo;
DROP
Fo2Me=# CREATE TABLE dirinfo(
Fo2Me(# code VARCHAR(8) PRIMARY KEY,
Fo2Me(# level SMALLINT NOT NULL,
Fo2Me(# name TEXT NOT NULL,
Fo2Me(# count SMALLINT NOT NULL DEFAULT 0
Fo2Me(# );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'dirinfo_pkey'
for table 'dirinfo'
CREATE
Fo2Me=# INSERT INTO dirinfo (code, level, name) VALUES('hbcol', 1, 'abc');
INSERT 63411 1
Fo2Me=# INSERT INTO dirinfo (code, level, name) VALUES('hbcrf', 1, 'zzz');
ERROR: Cannot insert a duplicate key into unique index dirinfo_pkey
I couldn't understand this error message. What's wrong?
The version of postgresql is 7.0.2 and the database created with EUC_KR encoding.
------
Park, Sungchul / mailto:scpark@gen128.com
gen128, inc. - The internet company powered by open source.
http://www.gen128.com / Voice : +82-2-3017-0128 / Fax : +82-2-3017-1128
238-9 poi kangnam, #601 poongjoen bldg., Seoul 135-250, Republic of Korea
> Fo2Me=# drop table dirinfo;
> DROP
> Fo2Me=# CREATE TABLE dirinfo(
> Fo2Me(# code VARCHAR(8) PRIMARY KEY,
> Fo2Me(# level SMALLINT NOT NULL,
> Fo2Me(# name TEXT NOT NULL,
> Fo2Me(# count SMALLINT NOT NULL DEFAULT 0
> Fo2Me(# );
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> 'dirinfo_pkey'
> for table 'dirinfo'
> CREATE
> Fo2Me=# INSERT INTO dirinfo (code, level, name) VALUES('hbcol', 1,
> 'abc');
> INSERT 63411 1
> Fo2Me=# INSERT INTO dirinfo (code, level, name) VALUES('hbcrf', 1,
> 'zzz');
> ERROR: Cannot insert a duplicate key into unique index dirinfo_pkey
>
> The version of postgresql is 7.0.2 and the database created with EUC_KR
>encoding.
And that's strange. I'm not sure about encodings, never used them....
Probably you should use another datatype to textual attributes? It's very
likely that when you're trying to enter 'hbcol', an escape sequence is
entered (2 bytes), and then each ASCII char also takes two octets.
Hmmmm.... So, it's truncated? If so, then with the first insert only escape
sequence and 'hbc' is inserted and this causes duplication for the second
insert?
Just a wild speculation. Someone with more experience would be more
helpfull.
--
contaminated fish and microchips
huge supertankers on Arabian trips
oily propaganda from the leaders' lips
all about the future
there's people over here, people over there
everybody's looking for a little more air
crossing all the borders just to take their share
planning for the future
Rainbow, Difficult to Cure
Thank you for your answer. > And that's strange. I'm not sure about encodings, never used them.... According to my last test. It seems not to be a solution changing encoding of database. I've created new test database with SQL_ASCII encoding and got same result. > Probably you should use another datatype to textual attributes? Same. I've tried CHAR and TEXT. It didn't solve my problem. > It's very likely that when you're trying to enter 'hbcol', an escape > sequence is > entered (2 bytes), and then each ASCII char also takes two octets. > Hmmmm.... So, it's truncated? If so, then with the first insert only escape > sequence and 'hbc' is inserted and this causes duplication for the second > insert? I don't think so. I've tried to insert other code 'hbcog'. It caused no error. > Just a wild speculation. Someone with more experience would be more > helpfull. thanks again. ------------- Park, Sungchul / mailto:scpark@gen128.com gen128, inc. - The internet company powered by open source. http://www.gen128.com / Voice : +82-2-3017-0128 / Fax : +82-2-3017-1128 238-9 poi kangnam, #601 poongjoen bldg., Seoul 135-250, Republic of Korea
> According to my last test. It seems not to be a solution changing > encoding of > database. I've created new test database with SQL_ASCII encoding and got > same > result. Very strange.... Works OK here on 7.0.0, database created with no explicit encoding. Actually, what I don't really like about the table is that attr named `count.' It's the name of an aggregate. Have you tried enabling full logging and monitoring what's going on? -- contaminated fish and microchips huge supertankers on Arabian trips oily propaganda from the leaders' lips all about the future there's people over here, people over there everybody's looking for a little more air crossing all the borders just to take their share planning for the future Rainbow, Difficult to Cure
Sungchul Park <scpark@gen128.com> writes:
> Fo2Me=# CREATE TABLE dirinfo(
> Fo2Me(# code VARCHAR(8) PRIMARY KEY,
> Fo2Me(# level SMALLINT NOT NULL,
> Fo2Me(# name TEXT NOT NULL,
> Fo2Me(# count SMALLINT NOT NULL DEFAULT 0
> Fo2Me(# );
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'dirinfo_pkey'
> for table 'dirinfo'
> CREATE
> Fo2Me=# INSERT INTO dirinfo (code, level, name) VALUES('hbcol', 1, 'abc');
> INSERT 63411 1
> Fo2Me=# INSERT INTO dirinfo (code, level, name) VALUES('hbcrf', 1, 'zzz');
> ERROR: Cannot insert a duplicate key into unique index dirinfo_pkey
> The version of postgresql is 7.0.2 and the database created with EUC_KR encoding.
I cannot duplicate this failure with current REL7_0 sources
(7.0.3-to-be) and EUC_KR encoding. Either it's been fixed since
7.0.2 (but there are no likely-looking patches in the CVS logs),
or there is something platform- or environment-specific about the
problem.
One possible environment issue: what LOCALE are you running the
postmaster in? (Check environment variables used when postmaster
is started for LOCALE or LC_xxx variables.)
I don't believe you mentioned what platform you are on, either.
regards, tom lane
Thank you. Mr. Tom lane. > One possible environment issue: what LOCALE are you running the > postmaster in? (Check environment variables used when postmaster > is started for LOCALE or LC_xxx variables.) You are right. it was because of LOCALE. I upgraded a locale package to newer one and it solved my problem. Thanks again. ---------------- Park, Sungchul / mailto:scpark@gen128.com gen128, inc. - The internet company powered by open source. http://www.gen128.com / Voice : +82-2-3017-0128 / Fax : +82-2-3017-1128 238-9 poi kangnam, #601 poongjoen bldg., Seoul 135-250, Republic of Korea