duplicate key ? (fwd)

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

Hi:

On Sat, 21 Oct 2000, Sungchul Park wrote:

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.

I just ran:

drop table dirinfo;
CREATE TABLE dirinfo(
code VARCHAR(8) PRIMARY KEY,
level SMALLINT NOT NULL,
name TEXT NOT NULL,
count SMALLINT NOT NULL DEFAULT 0
);
INSERT INTO dirinfo (code, level, name) VALUES('hbcol', 1, 'abc');
INSERT INTO dirinfo (code, level, name) VALUES('hbcrf', 1, 'zzz');

without problems:

pruebas=# \i prueba.sql
psql:prueba.sql:1: ERROR: Relation 'dirinfo' does not exist
psql:prueba.sql:7: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'dirinfo_pkey' for table
'dirinfo'
CREATE
INSERT 208144 1
INSERT 208145 1
pruebas=# select * from dirinfo ;
code | level | name | count
-------+-------+------+-------
hbcol | 1 | abc | 0
hbcrf | 1 | zzz | 0
(2 rows)

I'm using RH 6.2, and pgsql v. 7.0.2 with Spanish locale.

Just my 2 cents.

Roberto Andrade Fonseca
randrade@abl.com.mx

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